Google Sheets: Multiple Criteria with ArrayFormula/SUM/IF/COUNTIFS

[jasonjack]

Hi,

Is it possible to have more than 2 criteria within the following formula?

CELL G7: =ArrayFormula(SUM(IF("APAC"=A2:A11, 1/COUNTIFS(A2:A11,"APAC",D2:D11,D2:D11,E2:E11,G6)),0))

The aim is to count the number of unique occurrences of APAC in weight 18.

CELL G8: =ArrayFormula(SUM(IF(FREQUENCY(IF(E2:E11=G6,IF(A2:A11=F7,MATCH(C2:C11,C2:C11,0))),ROW(C2:C11)-ROW(C2)+1),C2:C11)))

This then looks at the unique occurrences of APAC and sums the total minutes.

I would like to add Column D as another criteria within each calculation.

Many Thanks

 

[RoryA]

What exactly constitutes a unique occurrence of APAC?

 

[jasonjack]

What exactly constitutes a unique occurrence of APAC?

This would be Column C (Minutes) and Column D (Supplier) and Column E (Week)

 

[RoryA]

Since you're filtering for a specific week and supplier, you could use:

=COUNTUNIQUE(FILTER(C2:C11,(A2:A11="APAC")*(E2:E11=G6)*(D2:D11="Supplier")))

 

[jasonjack]

Since you're filtering for a specific week and supplier, you could use:

=COUNTUNIQUE(FILTER(C2:C11,(A2:A11="APAC")*(E2:E11=G6)*(D2:D11="Supplier")))

Not quite:

For Week 18 I would to know the total number of occurrences of APAC where there is a unique number of Minutes.

For example Week 18 for APAC would have 5 occurrences: 1 x 36, 1 x 74, 1 x 100, 1 x 200 and 1 x 500.

However, I may want to break that down further to include Supplier for APAC giving me 4 occurrences: 2 x ABC1 (36 and 74 Minutes) 1 x ABC2 (100), 1 x ABC3 (200) and 1 x ABC4 (500).

 

[RoryA]

It was the latter that I was addressing since I thought that was the intent of your question. For the former, you'd just move the D column into the first filter argument:

=COUNTUNIQUE(FILTER(C2:D11,(A2:A11="APAC")*(E2:E11=G6)))