1. ## SUMIF (XP)

My worksheet has in column C a departmental name (there are 7 departments) and 4 data columns H, I, J, K with different methods of payments.

I want to to calculate the total payments by each department in column C combined for all methods of payment In columns H,I,J, and K). I have used SUMIF successfully for 1 column of data but can't get it to work for 4 columns.

Would appreciate any suggestions.

2. ## Re: SUMIF (XP)

=SUMPRODUCT((C2:C100="DEPT")*(H2:K100))

3. ## Re: SUMIF (XP)

Sorry but don't understand the comment "DEPT". Is this the specific dept name or does it refer to a defined range? and are the " " required?

4. ## Re: SUMIF (XP)

Dept is the name of the department you are trying to sum. If the department is text, yes the "" are required.

5. ## Re: SUMIF (XP)

DEPT should be replaced by one of the department names, and yes, it should be in quotes. You need such a formula for each department.

Another possible method is to create a pivot table with department in the row area, and four items, corresponding to each of the payment methods, in the data area.

6. ## Re: SUMIF (XP)

Thanks to both of you

7. ## Re: SUMIF (XP)

In addition ot the SUMPRODUCT formula for multiple criteria, a "simple" solution may be to just use a Pivot table. This will summarize all the data as desired and you won't have to list each of the particular departments. The pivot will automatically extract a unique list and it will be updated when the pivot is refreshed if new data is added.

Steve

8. ## Re: SUMIF (XP)

quick question - what's the advantage of using the sumproduct function rather than sumif?

9. ## Re: SUMIF (XP)

SUMPRODUCT uses this formula:

<pre>=SUMPRODUCT((C2:C100="DEPT")*(H2:K100))
</pre>

To do the same thing with SUMIF would require:

<pre>=SUMIF((C2:C100="DEPT")*(H2:H100))+SUMIF((C2: C100="DEPT")*(I2:I100))+SUMIF((C2:C100="DEPT")*(J2 :J100))+SUMIF((C2:C100="DEPT")*(K2:K100))
</pre>

There are also many things that you can do with SUMPRODUCT that can not be done at all with SUMIF.

10. ## Re: SUMIF (XP)

that not necessarily the sumif formula i would use. mine would be

SUMIF(\$C\$2:\$C\$100,"DEPT",H\$2:H\$100)

and i'd copy it for each column and/or dept i wanted to sum. i was under the impression each column would be summed individually, but i could be wrong.

11. ## Re: SUMIF (XP)

That formula would not give the result that the original poster wanted. That formula will sum only column H. The question was to sum columns H, I, J, and K when C is equal to "DEPT".

#### Posting Permissions

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