Sumif resource sheet reporting

[johnm]

I have the following table:

A B C D E F
Department D Chargable=1 Rate Mon Tue
non=0
1 Projects AA 1 100 1.5 2
2 Projects AA 0 50 0.5
3 Projects AB 1 50 2
4 SUPPORT AC 1 50

I need to calculate by department and ID the chargable and
unchargable costs.

For example AA chargable project time is 100*1.5=150 on
Monday and Unchargable is 50*0.5=25 and on Tuesday
100*2=200 and AB 50*2=100 Hence giving summary Department
costs for "PROJECTS" of 450 chargable and 25 unchargable
and AA chargable 350 and unchargable 25......

I have tried the following but there are two errors. FIrst
the IF C1:C4 is an array, and it does not like it and
second the Rate range cannot be an array.
I think Im biting off more than I can Chew in one
step...any suggestions please?
IF(C1:C4,"1",SUMIF($A$1:$A$4,"projects",$E$1:$F$4)*D14,0)

 

[Steve Smallman]

John,

while one of the guru's may be able to come up with an array formula for
you, I approach this rather differently.

I would add columns to multiply the data out, then use data sort to bring
the data together (using multiple sort criteria), and then put in a number
of subtotals using Data/Subtotal to summarise the data.

Given your smaple below, you will need to subtotal by Department, and then
by D and then by Chargeable/non (having sorted the data using that criteria
and sort order), make sure you remove the tick beside replace current
subtotals, and ensure you have ticked the sum function and all the total
columns. You cna have the summary above or below the data and put a page
break between the groupings if you wish.

HTH
Steve