Sumif with multiple criteria (XP)

Topic

New Reply

Hi – I have a column of numbers that I want to add up but only if the associated date is greater than a selected date AND a ‘Y’ appears in another column. I have a sumif function set up to sum the numbers if the date is greater than a selected date and I was trying to add the second criteria. Does sumif work like this or do I have to use some other function – or create a unique column that returns TRUE based on both tests?

Reply | Quote

Replies

SUMIF in combination with a column with TRUE/FALSE values that combine the conditions is one way.
You can also use SUM or SUMPRODUCT. For example, let’s say that the dates are in A1:A100, the “Y”s in B1:B100 and the numbers to be added in C1:C100. The comparison date is in F1.

=SUMPRODUCT((A1:A100>F1)*(B1:B100=”Y”)*C1:C100)

or as an array formula (confirm with Ctrl+Shift+Enter):

=SUM(IF((A1:A100>F1)*(B1:B100=”Y”),C1:C100))

Reply | Quote