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?

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?