Another Option Instead of Using Multiple SumIfs?

[jaydee]

Hi guys,

I am trying to find another way to sum data besides using multiple SumIf statements.

The workbook attached has a copy of a file I import monthly. I need to combine several of these rows into one category description (i.e rows 8-33 equals “Sales”, rows 49-126 equals “Cost of Sales”, rows 46-47 equals “Discounts”, etc).

I could use sum if to get the totals, however the formula gets long and hard to read. Plus, if additional GL accounts are added or removed, editing the formula would be a nightmare, ie for each GL Acct use SUMIF($A:$A,4000.000,$C:$C)+SUMIF($A:$A,4000.001,$C:$C), etc, etc

The problem is I have six subsidiaries so I have to import the file 6 times, but the exact range locations aren’t the same.

I can’t think of any other way to do this besides manually creating the sum if formulas on a seperate worksheet, which I could then dump in each file to get the totals which would be picked up on a summary wks. Is there any array or UDF that might be easier? I just need an example for one range as I can sort of read coding.

Thanks in advance.

[Teeroy]

If you are trying to collate sales, cost of sales etc. and have excel 2007 or later try =SUMIFS($C:$C,$A:$A, ">=4000",$A:$A,"<5000"), this will give you sales, and so on. You should be able to refine the criteria to get whatever splits you are after.

If you don't have excel 2007 or later, you could change the GL numbers to text and use wildcards in the SUMIF, such as =SUMIF($A:$A,"4*.???",$C:$C ), but it is less elegant.

[parttime_guy]

Hi Jaydee,

Attached is a solution hope you find useful.
Kindly follow the instructions carefully.

Happy Excelling
Regards

[dantzu]

SUMPRODUCT is another really great formula that accompishes CountIf, SumIf and LookUp results. Plus it has some advantages: I believe SUMPRODUCT works with closed workbooks unlike the other three.

[jaydee]

Thanks guys, especially teeroy (always helping me out ^_^) and parttime_guy. Both worked great, saved me a bunch of time! Have a great weekend!