SUMPRODUCT Multiple Criteria with Distinct Values

Hi everyone, I am hoping to get some assistance with a SUMPRODUCT formula with multiple criteria.

In the attached example s/sheet I am trying to count the number of distinct ‘Tests’ in column B (= 8) that also meets the adjacent ‘Result’ criteria (= Result 1), in column C (answer = 7 which I have manually counted to illustrate the requirement).

The independent components of my formula seem to work:

• Counting distinct Values only (case insensitive) in column B
• Counting Test results that = “Result 1” in column C

I need help with combining the formula.

Thanks in advance,
KJ

Draft formula:

=SUMPRODUCT((--EXACT(C3,C6:C18)),(--(B6:B18<>""))/COUNTIF(B6:B18,B6:B18&"")+0)

Since you are using 365, can you use this formula instead of SUMPRODUCT?

=COUNTA(UNIQUE(FILTER($B$6:$C$18,$C$6:$C$18=$C$3)))/2

Originally Posted by Gregb11

Since you are using 365, can you use this formula instead of SUMPRODUCT?

=COUNTA(UNIQUE(FILTER($B$6:$C$18,$C$6:$C$18=$C$3)))/2

Thanks for this! In this instance I am actually having to use Office 2016 and I can't get the above to function.

I am also using this within the context of a SUBTOTAL/OFFSET to calculate for visible cells only (based on some other filtering required), therefore, I will need to use a SUMPRODUCT which currently looks like this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C6:C18,ROW(C6:C18)-MIN(ROW(C6:C18)),,1)),(--EXACT($C$3,C6:C18)),(--(B6:B18<>""))/COUNTIF(B6:B18,B6:B18&"")+0)

Please let me know,
KJ

Originally Posted by CARACALLA

f5=SUM(IF(FREQUENCY(IF($C$6:$C$18=C3,MATCH(B6:B18,B6:B18,0)),ROW(B6:B18)-ROW(B6)+1),1))

control+shift+enter

Thanks, this appears to work in isolation, but I am hoping to build into a SUMPRODUCT to maintain the ability to calculate for visible cells only - is there a way to combine this in to a SUMPRODUCT?

F5=sum(if(frequency(if(subtotal(3,offset(b6,row(b6:b18)-row(b6),,1)),if(c6:c18=c3,match(b6:b18,b6:b18,0))),row(b6:b18)-row(b6)+1),1))

Originally Posted by CARACALLA

F5=sum(if(frequency(if(subtotal(3,offset(b6,row(b6:b18)-row(b6),,1)),if(c6:c18=c3,match(b6:b18,b6:b18,0))),row(b6:b18)-row(b6)+1),1))

Thanks so much, this is great! I have added Reputation

However, I haven't used this method before for these types of counts so unsure how to add more variables, whereas I am more familiar with a SUMPRODUCT.

I am also trying to avoid array formula's where possible, do you think this is possible to do in a SUMPRODUCT?

Alternatively, is there an explanation for how I can easily add extra variables to this formula? I have attached an updated s/sheet (v2) with the subsequent variables I am trying to add in to the formula.

Thanks again for the help here!

H5=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B6,ROW(B6:B18)-ROW(B6),,1)),IF(C6:C18=C3,MATCH(B6:B18,B6:B18,0))),ROW(B6:B18)-ROW(B6)+1),1))

Control+shift+enter



K5=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B6,ROW(B6:B18)-ROW(B6),,1)),IF(Table1[Results]=C3,IF(Table1[Rating]=D3,MATCH(B6:B18,B6:B18,0)))),ROW(B6:B18)-ROW(B6)+1),1))


Control+shift+enter


N5=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(B6,ROW(B6:B18)-ROW(B6),,1)),IF(Table1[Results]=C3,IF(Table1[Rating]=D3,IF(Table1[Expectation]=E3,MATCH(B6:B18,B6:B18,0))))),ROW(B6:B18)-ROW(B6)+1),1))


Control +shift+enter


Sorry for my English

For evry condition one if

For the Frequency

ROW (B6: B18) -ROW (B6) +1 bin arry

data array

IF (SUBTOTAL (3, OFFSET (B6, ROW (B6: B18) -ROW (B6) ,, 1))), IF (Table1 [Results] = C3, IF (Table1 [Rating] = D3, IF (Table1 [Expectation ] = E3, MATCH (B6: B18, B6: B18,0)))))