Countif with Two Criteria

F

ForSale

I have on sheet1 a couple thousand rows of data. A:A contains dates,
B:B contains ID numbers.
on sheet2 I need to know how many instances an ID number shows up on a
selected date. The specified ID number will be in sheet2, A2; the
specified date will be in sheet2, A1. It seems like I want a Countif,
but it only allows for one criteria as far as I know. What kind of
formula do I need for this?
Thanks.
 
J

Joerg

ForSale said:
I have on sheet1 a couple thousand rows of data. A:A contains dates,
B:B contains ID numbers.
on sheet2 I need to know how many instances an ID number shows up on a
selected date. The specified ID number will be in sheet2, A2; the
specified date will be in sheet2, A1. It seems like I want a Countif,
but it only allows for one criteria as far as I know. What kind of
formula do I need for this?
Thanks.
Click to expand...


Please have a look at array formulas
(http://www.cpearson.com/excel/array.htm)
This technique allows you to SUM all instances, where an ID number matches a
given date.

Joerg
 
B

Biff

Hi!

Try this:

=SUMPRODUCT(--(Sheet1!A2:A2000=Sheet2!A1),--(Sheet1!B2:B2000=Sheet2!A2))

You can't use entire columns with Sumproduct. A:A

If the formula will be on Sheet2 you don't need to include the sheet name in
the reference:

=SUMPRODUCT(--(Sheet1!A$2:A$2000=A1),--(Sheet1!B$2:B$2000=A2))

Biff
 
Top