Sum If - 2 conditions

P

penguin77

I want to sum a # field if the 2 conditions are met. 1 - the month =
February and 2, the $'s are sales


Data is like this

D F G
Month Measure(Sales) Data I want to add
 
J

JLatham

In Excel 2007 you could use SUMIFS() but SUMPRODUCT() works for 2007 and
earlier versions:
=SUMPRODUCT(--($D$2:$D$100="February"), --($F$2:$F$100=500),($G$2:$G100))
it would be more flexible if you had a cell to enter the month and sales
amount into, then you could write it something like:
=SUMPRODUCT(--($D$2:$D$100=X1), --($F$2:$F$100=X2),($G$2:$G100))
where X1 had "February" or another month in it, and
X2 had the sales amount you're interested in it.

You can also change the = test for the column F tests to >= or <= or even
expand the formula to 'filter' by sales ranges as:
=SUMPRODUCT(--($D$2:$D$100="February"), --($F$2:$F$100>=500),
--($F$2:$F$100<=1000),($G$2:$G100))
which would sum data for all Feb sales from $500 through $1000.

Hope this helps at least a little.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top