jaydee

11-01-2012, 06:18 PM

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.

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.