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))

[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




"Gunjani" <gunjani786@hotmail.com> wrote in message
news:1142735692.514030.191340@v46g2000cwv.googlegroups.com...
> 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
>

[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.

[Dave Peterson]

Maybe...

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



Gunjani wrote:
>
> 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

--

Dave Peterson

[Dave Peterson]

And I forgot the singleton L, too.

Dave Peterson wrote:
>
> Maybe...
>
> =SUM(SUMIF($D$5:$D$156,{"ee","e","m","ll"},$U$5:$U$156))
>
> Gunjani wrote:
> >
> > 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
>
> --
>
> Dave Peterson

--

Dave Peterson

[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


"Gunjani" wrote:

> 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
>
>