Summarizing with multiple conditions - Formula solution needed

[Daniel]

Hi,

I'm wondering if anybody could help me:

I need to put data from one format into another. The data is organised by
period and GL account. I want to automatically put it into a report that has
the same periods but each category in the report consists of several GL
accounts. I've mapped the GL codes to categories.

CL Cat account descrip 1 2 3
51 51311700 Protection 54 23 142
51 51312100 Household
51 51312200 Stationery 18 757 -442

So I'd like to have a formula that kind of works like a sumif in the CL
category, but also uses the periods (1-2-3) do determine which month i'm
looking at. I've set up the report i want to use in a way that each row is
identified by the 'CL Cat' number and each column with a period number. So
the formula should sum all CL Cat 51's in column 1 and should yield the
result 72 in period 1, 780 in period 2, etc.

I hope this is understandable, if not I'll try to explain further

Thanks

 

[Per Jessen]

Hi

This SumProduct formula is what you need CL Cat 51 and period 1:

=SUMPRODUCT((A2:A4=51)*(D1:F1=1)*(D2:F4))

Regards,
Per

 

[Max]

One way - use SUMIF with the sum range made flexible via an OFFSET
Assume your source table as posted is in sheet: x,
in cols A to F, data from row 2 down to row 100
In another sheet,
In B1 across are the periods: 1, 2, 3 ...
In A2 down are the CL Cats, eg: 51, 52, etc
Put in B2:
=SUMIF(x!$A$2:$A$100,$A2,OFFSET(x!$A$2:$A$100,,MATCH(B$1,x!$1:$1,0)-1))
Copy across/fill down to return the required results. voila? eternalize the
joy, hit the YES below