As before
Setup thes cells
B34: PST
C34: LTR
D34: DEH
A35: 301
A36: 302
A37: 303
A38: 304
A39: 305
In B35, add
=SUMPRODUCT(--($A$2:$A$30=B$35),--($BŁ2:$B$30=$A35),$E$2:$E$30)
and copy across and down to D39
--
HTH
RP
(remove nothere from the email address if mailing direct)
"andrewo-s" wrote in message
...
Thanks for the suggestion ... I tried SUMPRODUCT ... but I think it's not
what I'm after.
I don't think my previous question was specific enough. By way of
example:
Column A = one of 3 area codes (e.g. "PT", "GR"& "SK")
Column B = one of 5 account codes (e.g. "301", "302"...)
Column C&D = unit cost & quantity per line item
Column E = total cost per line item
Lets say there are 29 rows of data (rows 2-30)
What I want to do is make one summary table where for each area code the
table sums the total for each of the 5 account codes. i.e. there are two
criteria: area code and account code.
If there is just one criteria (e.g. account code) then I know how to sum
each account code into a summary table by using the formula =SUMIF(range1,
criteria1, range 2) where in this case range1 is A2:A30 and range 2 is the
corresponding E2:E30 - and where criteria 1 is also a range. eg in cells
A35:A39 I put in the 5 account codes "301". then I copy the above formula
from cells B35 to B39 - where the formula in cell B35 would be
=SUMIF(A2:A30,A35,E2:E30) and the final cell, B39, it'd be
=SUMIF(A2:A30,A39,E2:E30).
But I can't work out how to refer to two criteria at the same tame.
What I want to do is set up 3 columns in the summary table - B35:B39
referring to area code PST, C35:C39 referring to area code LTR and D35:D39
referring to area code DEH.
Any further suggestions ?
|