Conditional Sums

  • Thread starter Thread starter Peter J Elliott
  • Start date Start date
P

Peter J Elliott

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
 
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
 
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
 
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:[email protected]]
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
 
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 said:
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:[email protected]]
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 said:
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
Click to expand...
Click to expand...
 
Grand!

Cheers Ron - all up and working fine.

Best regards

Peter

-----Original Message-----
From: Ron Coderre [mailto:[email protected]]
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 said:
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:[email protected]]
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 said:
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
Click to expand...
Click to expand...
 
Back
Top