Conditional Sums

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter J Elliott - 16 Feb 2007 17:46 GMT

I have a table containing numerical values in column D and text in
column E.

The text in column E can be either S, M or P

In cell D31 I want to total the values in column D corresponding to the
letter S in column E
In cell D32 I want to total the values in column D corresponding to the
letter M in column E
In cell D33 I want to total the values in column D corresponding to the
letter P in column E

I've tried using COUNTIF and the Conditional Sum Wizard, but I can't get
something that does the job.

Could someone please suggest a formula to do the job?

Many thanks

Regards

Peter

Reply to this Message

Ron Coderre - 16 Feb 2007 18:00 GMT

Try something like this:

With
D2:D30 containing numbers
E2:E30 containing one of S, M, or P

These formulas return the sum of Col_D cells where the corresponding Col_E
cell value matches a specific letter
D31: =SUMIF(D2:D30,"S",E2:D30)
D32: =SUMIF(D2:D30,"M",E2:D30)
D33: =SUMIF(D2:D30,"P",E2:D30)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

> I have a table containing numerical values in column D and text in
> column E.
[quoted text clipped - 21 lines]
>
>

Reply to this Message

Ron Coderre - 16 Feb 2007 18:01 GMT

Darn! I reversed the ranges.....

The formula should be:
D31: =SUMIF(E2:D30,"S",D2:D30)
D32: =SUMIF(E2:D30,"M",D2:D30)
D33: =SUMIF(E2:D30,"P",D2:D30)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

> I have a table containing numerical values in column D and text in
> column E.
[quoted text clipped - 21 lines]
>
>

Reply to this Message

Peter J Elliott - 16 Feb 2007 18:21 GMT

Hmmmmmmm

Thanks Ron but there appears to be something funny going on......

I tried your revised formulae but, the moment I hit enter, =SUMIF(E2:D30
changes to =SUMIF(D2:E30.

I've tried entering the date manually and copying & pasting your
formulae, but it changes every time.

Any ideas?

Regards

Peter

-----Original Message-----
From: Ron Coderre [mailto:ronREMOVETHIScoderre@bigfoot.com]
Posted At: 16 February 2007 18:01
Posted To: microsoft.public.excel.newusers
Conversation: Conditional Sums
Subject: RE: Conditional Sums

Darn! I reversed the ranges.....

The formula should be:
D31: =SUMIF(E2:D30,"S",D2:D30)
D32: =SUMIF(E2:D30,"M",D2:D30)
D33: =SUMIF(E2:D30,"P",D2:D30)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

"Peter J Elliott" wrote:

> I have a table containing numerical values in column D and text in
> column E.
[quoted text clipped - 20 lines]
>
>

Reply to this Message

Ron Coderre - 16 Feb 2007 19:08 GMT

Yes....in my haste to correct my formulas, I introduced a different error.
What's that old saying?:
"The hurrier I go, the behinder I get"

These work properly.
D31: =SUMIF(E2:E30,"S",D2:D30)
D32: =SUMIF(E2:E30,"M",D2:D30)
D33: =SUMIF(E2:E30,"P",D2:D30)

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

XL2002, WinXP

> Hmmmmmmm
>
[quoted text clipped - 59 lines]
> >
> >

Reply to this Message

Peter J Elliott - 17 Feb 2007 08:22 GMT

Grand!

Cheers Ron - all up and working fine.

Best regards

Peter

-----Original Message-----
From: Ron Coderre [mailto:ronREMOVETHIScoderre@bigfoot.com]
Posted At: 16 February 2007 19:08
Posted To: microsoft.public.excel.newusers
Conversation: Conditional Sums
Subject: Re: Conditional Sums

Yes....in my haste to correct my formulas, I introduced a different
error.
What's that old saying?:
"The hurrier I go, the behinder I get"

These work properly.
D31: =SUMIF(E2:E30,"S",D2:D30)
D32: =SUMIF(E2:E30,"M",D2:D30)
D33: =SUMIF(E2:E30,"P",D2:D30)

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

XL2002, WinXP

"Peter J Elliott" wrote:

> Hmmmmmmm
>
[quoted text clipped - 45 lines]
> >
> > I've tried using COUNTIF and the Conditional Sum Wizard, but I can't

> > get something that does the job.
> >
[quoted text clipped - 8 lines]
> >
> >

Reply to this Message