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.


--
ForSale
------------------------------------------------------------------------
ForSale's Profile: http://www.excelforum.com/member.php...o&userid=11896
View this thread: http://www.excelforum.com/showthread...hreadid=574454

"ForSale" wrote in
message ...

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.


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

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

"ForSale" wrote in
message ...

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.


--
ForSale
------------------------------------------------------------------------
ForSale's Profile:
http://www.excelforum.com/member.php...o&userid=11896
View this thread: http://www.excelforum.com/showthread...hreadid=574454