SUMIF simplified

G

Gunjani

Currently I am using the following formulae

=SUMIF($D$5:$D$156,"=EE",$U$5:$U$156)
=SUMIF($D$5:$D$156,"=E",$U$5:$U$156)
=SUMIF($D$5:$D$156,"=M",$U$5:$U$156)
=SUMIF($D$5:$D$156,"=L",$U$5:$U$156)
=SUMIF($D$5:$D$156,"=LL",$U$5:$U$156)

Then SUM all the above to get the overall total. How can this be
simplified to one formula.

I have tried the folloing but with no joy

=SUMIF($D$5:$D$156,"=EE,E,M,L,LL",$U$5:$U$156)
=SUMIF($D$5:$D$156,"=EE","=E","=M","=L","=LL", $U$5:$U$156)
=SUMIF($D$5:$D$156,OR(EE,E,M,L,LL),$U$5:$U$156)

but none of the above work.

any suggestions
Thx
Gunj
 
D

daddylonglegs

You can do it like this

=SUM(SUMIF($D$5:$D$156,{"EE","E","M","L","LL"},$U$5:$U$156)
 
R

Ron Coderre

Try something like this:

A1: =SUMPRODUCT((($D$5:$D$156)={"M","LL","L","EE","E"})*$U$5:$U$156)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
G

Gunjani

Thx, the same does not work for COUNTIF

i.e COUNTIF(B5:B121,{"EE";"E";"M";"L";"LL"})
This only returns a value the same as COUNTIF(B5:B121,"EE")

Thx
Gunj
 
P

Peo Sjoblom

You need to use sum as well, if you look at the formula you were given it is
wrapped in SUM
you need to do the same with countif

=SUM(COUNTIF(B5:B121,{"EE";"E";"M";"L";"LL"}))

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon
 
V

vane0326

Try this!

=SUMPRODUCT(--(ISNUMBER(MATCH($D$5:$D$156,{"EE","E","M","L","LL"},0))),$U$5:$U$156)

just hit enter.


If you want to count then use this!

=SUMPRODUCT(--(ISNUMBER(MATCH($D$5:$D$156,{"EE","E","M","L","LL"},0))))


just hit enter
 
Top