SUMIF MULTIPLE ARRAY CRITERIA
Try this:
A1:A14 =
PS2,PM2,PL2,PS4,PM4,PL4,CS2,CM2,CL2,CS4,CM4,CL4,T7 ,T10
B1:B3 = Prisons,CJJ,Aims
Then:
=SUMPRODUCT(--(ISNUMBER(MATCH(easi_cat,A1:A14,0))),--(ISNUMBER(MATCH(bill.code,B1:B3,0))),easi_pp)
Biff
"Santa-D" wrote in message
ps.com...
I've got three ranges
bill.code
easi_pp
easi_cat
What I want to do is SUM a number of vehicles that fall within various
categories
easi_cat={"PS2","PM2","PL2","PS4","PM4","PL4","CS2 ","CM2","CL2","CS4","CM4","CL4","T7","T10"}
and if they are in certain areas
bill.code={"PRISONS","CJJ","AIMS"}
and the value to sum is in easi_pp
I've tried SUMPRODUCT but that didn't do the work (and I don't think it
was meant to)
SUM(IF formulas didn't work unless it had one array
and this didn't work
=SUM(IF(AND((easi_cat={"PS2","PM2","PL2","PS4","PM 4","PL4","CS2","CM2","CL2","CS4","CM4","CL4","T7", "T10"})*(bill.code={"PRISONS","CJJ","AIMS"})),easi _pp,0))
Now this formula works.....
=SUM(IF((easi_cat={"PS2","PM2","PL2","PS4","PM4"," PL4","CS2","CM2","CL2","CS4","CM4","CL4","T7","T10 "})*(bill.code="PRI
SERV"),easi_pp,0)) but doesn't work when I go bill.code={"1","2","3"}
is there a way aroudn it?
|