excelsumproduct

Formula to count alternate columns given specific value

In the spreadsheet attached, I am try to get a percent and count of values given the detection code associated with it in the adjacent column (U, J+, R, etc.). If there is a number with no code, it counts as detected. If there is a number with the letter U, it counts as U, etc.

I need to do this for all rows for the spreadsheet. This is a snapshot of a much larger dataset, so I'd like to automate it as much as possible.
Units Chemical/Sample ID 1 Detection code 2 Detection code 3 Detection code 4 Detection code 5 Detection code 6 Detection code 7 Detection code Percent Detected Count Detected Percent U Count U Percent J Count J Percent R Count R Count per Row
ug/mL 4-Nitrophenol 0 0 0
ug/mL Acetaldehyde 260 10 U 1 1 2
ug/mL Aluminum 7700 200 U 1 1 2
ug/mL Arsenic 1.4 1 U 1 1 2
ug/mL Benzo(a)anthracene 0 0 0
ug/mL Benzo(a)pyrene 10 U 0.19 U 0.19 U 0 3 3
ug/mL Benzo(g,h,i)perylene 6.7 1 0 1
mg/L Calcium 380 1
mg/L Chloride 0.19 U 110 96 3
ug/mL Chloroform 200 1 U 1 U 3
ug/mL Chloromethane 0.19 U 1
ug/mL Chromium-Hexavalent 0.5 U 10 U 10 U 3
ug/mL Cobalt 6.7 0.3 U 2
ug/mL Copper 96 380 18 3
ug/mL Dibenz(a,h)anthracene 1 U 0.19 U 0.19 U 3
ug/mL Formaldehyde 200 10 U 2
ug/mL Hexachloroethane 10 U 1
ug/mL Hydrazine 0.3 U 0.5 U 0.5 U 3
ug/mL Iron 18 22000 160 3
ug/mL Manganese 0.19 U 8700 59 3
ug/mL Nickel 10 U 25 10 U 3
mg/L Nitrate 0.3 0.24 J+ 2
mg/L Nitrite 0.05 U 0.05 R 2
ng/L N-Nitrosodimethylamine (NDMA) 0.34 J 2.1 U 2 U 0.77 J 0.8 J 2.3 U 2.1 U 7
ng/L N-Nitroso-di-n-propylamine (NDPA) 2.2 U 2.1 U 2 U 2 U 2.1 U 5
ug/mL Phenanthrene 0
mg/L Sodium 0

I've used this code:

=SUMPRODUCT((ISNUMBER(C3:O3)*(ISBLANK(D3:P3))))

and

=SUMPRODUCT((ISNUMBER(C5:O5)*(ISTEXT(D5:P5))))

But I cannot figure out how to select for a specific text: "U", "J", "R", etc.

Solution

  • As per your expected result, it sounds like the Detection Code value doesn't matter. The following formula identifies the columns of interest checking that they are numbers. You can use the following formula:

    =LET(in, A1:Z28, h, TAKE(in,1), fn, 1-ISNA(XMATCH(ISNUMBER(h),TRUE)),
 BYROW(DROP(in,1), LAMBDA(x, SUM(N(FILTER(x, fn,0)<>"")))))

    You can also try the following which replaces BYROW array function with MMULT.

    =LET(in,A1:Z28, h,TAKE(in,1), fn, 1-ISNA(XMATCH(ISNUMBER(h),TRUE)),
 idx, FILTER(SEQUENCE(,COLUMNS(h)),fn), MMULT(N(CHOOSECOLS(DROP(in,1), idx)<>0), 
  SEQUENCE(COLUMNS(idx),,,0)))

    output