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



## jasonjack (May 23, 2021)

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 (May 24, 2021)

What exactly constitutes a _unique_ occurrence of APAC?


----------



## jasonjack (May 24, 2021)

RoryA said:


> What exactly constitutes a _unique_ occurrence of APAC?



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


----------



## RoryA (May 24, 2021)

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 (May 24, 2021)

RoryA said:


> 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 (May 24, 2021)

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)))
```


----------

