Calculating Sum with Multiple Field Criteria

[bpliskow]

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?

[SteveG]

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

[bpliskow]

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