Countif with Two Criteria

[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.

[Joerg]

"ForSale" <ForSale.2cz13v_1156299911.0602@excelforum-nospam.com> wrote in
message news:ForSale.2cz13v_1156299911.0602@excelforum-nospam.com...
>
> 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

[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

"ForSale" <ForSale.2cz13v_1156299911.0602@excelforum-nospam.com> wrote in
message news:ForSale.2cz13v_1156299911.0602@excelforum-nospam.com...
>
> 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
>