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, itd be
=SUMIF(A2:A30,A39,E2:E30).
But I cant 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 ?
|