SUMIF simplified

[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

 

[daddylonglegs]

You can do it like this

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

 

[Dave Peterson]

Maybe...

=SUM(SUMIF($D$5:$D$156,{"ee","e","m","ll"},$U$5:$U$156))

 

[Guest]

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

 

[Dave Peterson]

And I forgot the singleton L, too.

Maybe...

=SUM(SUMIF($D$5:$D$156,{"ee","e","m","ll"},$U$5:$U$156))

 

[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

 

[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

 

[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