sumif Function (Office 2002 SP2)

Topic

New Reply

I have a spreadsheet with 3 columns that have the following in them:

Quantity Code Revenue

I have done a sumif of the revenue based on the code value, and a sumif on the Quantity based on the code value. The last thing I would like to do is to do a sumif on the Quantity based on Code Value for those lines that have revenue greater than 0.

I tried to do this with the following:
=SUMIF($G$2:$G$180,”=VSC41105 && H2:h180>0″,$F$2:$F$180)

the middle entry in the ()’s is the problem area. How can I do this?

I know that I can create a hidden column where I put the quantity values only if there is no-zero revenue, but I wanted to avoid doing that. Any thoughts?

Thanks,

Andy

Reply | Quote

Replies

I could not figure out from your formual exactly what your spreadsheet looks like. So, I will have to show you how to do it using a different setup.

You can’t do this with SUMIF since it can not handle more than one condition. You will need an array formula. If the Quantity is in A2:A180, and the Code is in B2:B180, and the Revenue is in C2:C180, then the following array formula will sum the revenue where the Quantity=1, the Code=”A”, and the Revenue>0:

=SUM((A2:A180=1)*(B2:B180="A")*(C2:C180>0)*C2:C180)

Again, this is an Array Formula, so you must hold down the Ctrl and Shift keys when you press the Enter key to enter the formula.

Reply | Quote

I think you want (ARRAY confirm with ctrl-shift-enter):

=SUM(IF(($G$2:$G$180="VSC41105")*(H2:h180>0),$F$2:$F$180))

This will give you the sum of the items in F2:F180 where the rows in G2:G180 = “VSC41105” AND the values in H2:H180>0

Steve

Reply | Quote

I think you want (ARRAY confirm with ctrl-shift-enter):

=SUM(IF(($G$2:$G$180="VSC41105")*(H2:h180>0),$F$2:$F$180))

This will give you the sum of the items in F2:F180 where the rows in G2:G180 = “VSC41105” AND the values in H2:H180>0

Steve

Reply | Quote