# Thread: sumif Function (Office 2002 SP2)

1. ## sumif Function (Office 2002 SP2)

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

2. ## Re: sumif Function (Office 2002 SP2)

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:

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

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.

3. ## Re: sumif Function (Office 2002 SP2)

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

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•