|
Conditional Sums
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 |
Conditional Sums
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 "Peter J Elliott" wrote: 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 |
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. 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 |
Conditional Sums
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 ] Posted At: 16 February 2007 18:01 Posted To: microsoft.public.excel.newusers Conversation: Conditional Sums Subject: 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. 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 |
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 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 ] Posted At: 16 February 2007 18:01 Posted To: microsoft.public.excel.newusers Conversation: Conditional Sums Subject: 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. 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 |
Conditional Sums
Grand!
Cheers Ron - all up and working fine. Best regards Peter -----Original Message----- From: Ron Coderre ] Posted At: 16 February 2007 19:08 Posted To: microsoft.public.excel.newusers Conversation: Conditional Sums Subject: 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 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 ] Posted At: 16 February 2007 18:01 Posted To: microsoft.public.excel.newusers Conversation: Conditional Sums Subject: 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. 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 |
| All times are GMT +1. The time now is 01:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com