Calculating Sum with Multiple Field Criteria
 

I have a spreadsheet with the following values:

A B
01-JAN-06 John Smith
01-JAN-06 Jane Doe
01-FEB-06 John Smith
09-FEB-06 John Smith
etc.

Either in the same or new worksheet, I want to calculate the number of
times "John Smith" and "JAN" appear in the same row, "Jane Doe" and
"JAN", "John Smith" and "FEB", etc.

The expected results would appear as follows:

January
John Smith 1
Jane Doe 1

February
John Smith 2
Jane Doe 0

What is the formula needed to accomplish this?


--
bpliskow
------------------------------------------------------------------------
bpliskow's Profile: http://www.excelforum.com/member.php...o&userid=31645
View this thread: http://www.excelforum.com/showthread...hreadid=513351

Calculating Sum with Multiple Field Criteria
 

SUMPRODUCT will work.

=SUMPRODUCT(--(MONTH($A$1:$A$4)=1),--($B$1:$B$4="John Smith"))

Just change the condition for MONTH to equal the correct month number
as needed. (i.e. Jan=1, Feb=2, March =3 etc...). You could refer to
the names in cells rather than typing in the text ="John Smith" also.

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=513351

Calculating Sum with Multiple Field Criteria
 

Yup, that helps. Found the answer in the Microsoft Support Forum in
between my post and your reply.


--
bpliskow
------------------------------------------------------------------------
bpliskow's Profile: http://www.excelforum.com/member.php...o&userid=31645
View this thread: http://www.excelforum.com/showthread...hreadid=513351