Forum Discussion
countif additons across multiple rows
looking for a more efficient method to calculate training compliance for a particualr city branch:
I can limt to a particular city using
COUNTIF(C:C,"Canberra")
and then count the 'yes' returns for a particualr column with
COUNTIFS(C:C,"Canberra,D:D,"yes")
but I want to count all the 'yes' responses across all criteria for a given value, ie count all the 'yes' responses for Canberra.
COUNTIFS(C:C"Canberra,D:G,"yes") returns an invalid response (VALUE!) as the range size doesnt match, though I dont want to have to list out for every column as the real sheet has 20 training columns, ie:
COUNTIFS(C:C,"Canberra,D:D,"yes")+COUNTIFS(C:C,"Canberra,E:E,"yes")+COUNTIFS(C:C,"Canberra,F:F,"yes")... etc having to repeat for each additional column.
Is there a method to shorten this query?
| Name | ID | City | WHS? | Security? | Equity? | Payroll? |
| A | 123 | Canberra | y | y | y | y |
| B | 234 | Brisbane | y | y | y | n |
| C | 345 | Sydney | y | n | y | y |
| D | 456 | Canberra | n | y | n | y |
| E | 567 | Canberra | y | n | y | y |
| F | 678 | Sydney | y | n | y | y |
| G | 789 | Brisbane | n | y | y | y |
Any help greatly apprecaited.
Cheers
Shacks
- You could try:
=SUM((C:C="Canberra")*(D:G="y"))
If you don't have dynamic array excel version:
=SUMPRODUCT((C:C="Canberra")*(D:G="y"))
2 Replies
- You could try:
=SUM((C:C="Canberra")*(D:G="y"))
If you don't have dynamic array excel version:
=SUMPRODUCT((C:C="Canberra")*(D:G="y"))- thanks JMB17 - option 2 worked perfectly. I had tried SUM but must not have the higher spec version of excel.
cheers
