COUNTIF multiple criteria

[dschwister]

Hi All-

I am working in two worksheets. Worksheet A is where I am rolling up all of my data and Worksheet B is where I am inputting all of said data. In Worksheet A I am trying to find the number of times "david" appears in range "worksheetB A1:A100" and the number "1" in "worksheetB E1:E100". Both of these instances must occur for me to count. Does this make sense? I thought maybe a vlookup would work but it isn't. I also tried a sumproduct but that didn't work either. Any help would would be greatly appreciated!!!

What I need is for Excel to count the number of times "David" and "1" appear in the same row. David is column A and 1 would appear in column E.

 

[barry houdini]

Which version of Excel are you using?

In Excel 2007 and later you can use COUNTIFS with an "S", i.e.

=COUNTIFS('worksheetB'!A1:A100,"David",'worksheetB'!E1:E100,1)

or SUMPRODUCT in any version, i.e.

=SUMPRODUCT(('worksheetB'!A1:A100="David")*('worksheetB'!E1:E100=1))

Note that 1 wouldn't normally be in quotes unless the data in column E is formatted as text

 

[dschwister]

Hi Barry,

Thanks for your quick response. I am unfortunately working in Google docs, as I don't have Excel for work. COUNTIFS didn't work (it returned an error message) and SUMPRODUCT although it didn't return an error, I can see that the formula isn't working because it is miscounting.

in column a, i want to count the number of times "david" and "1" appear in a row. so "david" must appear in column A and "1" must appear in column E.

worksheet b
A B C D E
david 83 98 55 1
eric 87 98 55 3
david 83 98 55 3
eric 87 98 55 3
david 85 96 55 1

 

[barry houdini]

SUMPRODUCT works in googledocs but I understand that you can't use it for multi-criteria counting as you want, try using a formula like this

=ArrayFormula(SUM(IF(A1:A100=ʺDavidʺ,IF(E1:E100=1,1))))

You'll need to add in the sheet names......

 

[dschwister]

that worked perfectly! you the man Barry! thanks so much.

 

[dschwister]

Do you know how to have a "-" show up in Googledocs when zero is divided by zero?

SUMPRODUCT works in googledocs but I understand that you can't use it for multi-criteria counting as you want, try using a formula like this

=ArrayFormula(SUM(IF(A1:A100=ʺDavidʺ,IF(E1:E100=1,1))))

You'll need to add in the sheet names......

 

[dschwister]

right now I am getting a #DIV/0! error