Thread: Compound SUMIF

Good afternoon, I seem to be having a major brain fade at the moment..

My problem is that I have a worksheet tab (RawTimeSheetData) which contains a whole series of week/timecode values for a range of people.

I want to accumulate the hours for an invoice period / job code combination. As an example in the tab InvoicePeriodSummaryTimes cell D6 i want to sum all the hours from RawTimeSheetData where both cells A6 & B6 from InvoicePeriod tab = cells D6 & E6 from the rawdata tab.

I will probably kick myself when someone shows me just how simple it is (or alternatively how dumb I am

Cheers

Jmac1947

You might kick yourself even more when you find that you posted a password protected file and have to repost it so we can have a look

I guess your solution lies with sumproduct.

try in D6, copy down and across, adjust ranges to suit

Code:

=SUMPRODUCT(--(RawTimeSheetData!$E$6:$E$17=InvoicePeriodSummaryTimes!$A6),--(RawTimeSheetData!$D$6:$D$17=InvoicePeriodSummaryTimes!$B6),OFFSET(RawTimeSheetData!$E$6:$E$17,0,MATCH(RawTimeSheetData!F$4,InvoicePeriodSummaryTimes!$D$4:$L$4,0)))

hth

I would probably opt to use INDEX rather than OFFSET given the latter would make all the SUMPRODUCTs Volatile.

So on that basis perhaps:

Code:

D6:
=SUMPRODUCT(--(RawTimeSheetData!$E$6:$E$17=$A6),--(RawTimeSheetData!$D$6:$D$17=$B6),INDEX(RawTimeSheetData!$F$6:$Z$17,0,MATCH(D$4,RawTimeSheetData!$F$4:$Z$4,0)))
copied across matrix

However I would make the following points:

a) if your headers on reporting sheet are always to be in the same order as those on the RawTimeSheetData sheet then there's no need for INDEX either - simply use relative reference and copy across.

b) if a) does not hold true then you should consider storing the MATCH result in a header row (like the names) - refer to it thereafter rather then repeatedly calling the MATCH in each row in each column, eg:

Code:

InvoicePeriodSummaryTime!D3:
=MATCH(D$4,RawTimeSheetData!$F$4:$Z$4,0)
copied across to L3

(this value is constant per column so you should only really look to calculate once)

c) if your matrix is much bigger than this you would be best served concatenating columns D & E on RawTimeSheetData sheet such that you can revert to a more efficient SUMIF function, eg:

Code:

RawTimeSheetData!Z6
=$E6&"@"&$D6
copied down for all rows

at which point the expensive SUMPRODUCT can be dispensed with altogether and be replaced by:

Code:

InvoicePeriodSummaryTimes!D6
=SUMIF(RawTimeSheetData!$Z$6:$Z$17,$A6&"@"&$B6,INDEX(RawTimeSheetData!$F$6:$Z$17,0,D$3))
applied across matrix

in the above I've used the MATCH header value as outlined in point b) ... again if per point a) the column headers on the results tab are identical to those on the source tab then simply use relative referencing instead of the INDEX, eg:

Code:

=SUMIF(RawTimeSheetData!$Z$6:$Z$17,$A6&"@"&$B6,RawTimeSheetData!F$6:F$17)
copied across matrix

the concatenation approach is undoubtedly less elegant than the SUMPRODUCT but it is significantly more efficient when used in large volume or over large data sets.

I knew that was coming. I was going to post a non-volatile alternative after I'd done my chores, honest! Rushed the kids through dinner thinking "INDEX/MATCH!! Remember to post INDEX/MATCH!"