two conditions formula

[michelledean via OfficeKB.com]

I need help creating a formula that will return a count of Column B only if
Colum AH (date) and AG (Name) equal a particular date and a particular name.


example:

Column B Column AG Column AH
1 12/11/06 Dudley

 

[Guest]

=SUMPRODUCT(--(AH1:AH65000=yourdate),--(AG1:AG65000=yourname))
This will count the # of rows that meet the criteria of date and name.

SUMPRODUCT won't work on an entire row so you'll have to limit your range to
at least the entire column minus one row.

yourdate must be either a reference to a cell or the date in number format
(ex 2/21/07 = 39134

Hope this helps.

Bill Horton

 

[Guest]

Wonderful (for me, anyway)! Thank you.

=SUMPRODUCT(--(AH1:AH65000=yourdate),--(AG1:AG65000=yourname))
This will count the # of rows that meet the criteria of date and name.

SUMPRODUCT won't work on an entire row so you'll have to limit your range to
at least the entire column minus one row.

yourdate must be either a reference to a cell or the date in number format
(ex 2/21/07 = 39134

Hope this helps.

Bill Horton