|
sum conditional
how do I sum the following
if there is "Y" in any of the first 3 columns then take the value in 4th column, go to next row and if it contains "Y" in any of the first 3 columns then take the value. and so on.. and give a total of all rows taht contains "Y" .. N N N 369192 44258 N N Y 53660 34229 N Y N 150273 12998 N Y Y 1191 423 Y N N 22245 2184 Y N Y 99 36 |
sum conditional
Try something like this:
With your sample data in Cells A1:E6 The formula for total of Col_D values from rows that contain at least one "Y" in columns A through C is: G1: =SUMPRODUCT(ISNUMBER(SEARCH("Y",A1:A6&B1:B6&C1:C6) )*D1:D6) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "flow23" wrote: how do I sum the following if there is "Y" in any of the first 3 columns then take the value in 4th column, go to next row and if it contains "Y" in any of the first 3 columns then take the value. and so on.. and give a total of all rows taht contains "Y" . N N N 369192 44258 N N Y 53660 34229 N Y N 150273 12998 N Y Y 1191 423 Y N N 22245 2184 Y N Y 99 36 |
sum conditional
Hi,
One way: =SUMPRODUCT(--((A1:A6="Y")+(B1:B6="Y")+(C1:C6="Y")0)*D1:D6) Hope this helps. Regards, B. R. Ramachandran "flow23" wrote: how do I sum the following if there is "Y" in any of the first 3 columns then take the value in 4th column, go to next row and if it contains "Y" in any of the first 3 columns then take the value. and so on.. and give a total of all rows taht contains "Y" . N N N 369192 44258 N N Y 53660 34229 N Y N 150273 12998 N Y Y 1191 423 Y N N 22245 2184 Y N Y 99 36 |
sum conditional
many thanks it works great
but only if data is in the same sheet. I forogot to mention that data in a different sheet. however, I tried to alter the formula but it didnt work SUMPRODUCT(ISNUMBER(SEARCH("Y",raw_overview!A29:A3 4,raw_overview!B29:B34,raw_overview!C29:C34))*raw_ overview!A29:A34) anything wrong? "Ron Coderre" wrote: Try something like this: With your sample data in Cells A1:E6 The formula for total of Col_D values from rows that contain at least one "Y" in columns A through C is: G1: =SUMPRODUCT(ISNUMBER(SEARCH("Y",A1:A6&B1:B6&C1:C6) )*D1:D6) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "flow23" wrote: how do I sum the following if there is "Y" in any of the first 3 columns then take the value in 4th column, go to next row and if it contains "Y" in any of the first 3 columns then take the value. and so on.. and give a total of all rows taht contains "Y" . N N N 369192 44258 N N Y 53660 34229 N Y N 150273 12998 N Y Y 1191 423 Y N N 22245 2184 Y N Y 99 36 |
sum conditional
Try this:
=SUMPRODUCT(ISNUMBER(SEARCH("Y",raw_overview!A29:A 34&raw_overview!B29:B34&raw_overview!C29:C34))*raw _overview!D29:D34) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "flow23" wrote: many thanks it works great but only if data is in the same sheet. I forogot to mention that data in a different sheet. however, I tried to alter the formula but it didnt work SUMPRODUCT(ISNUMBER(SEARCH("Y",raw_overview!A29:A3 4,raw_overview!B29:B34,raw_overview!C29:C34))*raw_ overview!A29:A34) anything wrong? "Ron Coderre" wrote: Try something like this: With your sample data in Cells A1:E6 The formula for total of Col_D values from rows that contain at least one "Y" in columns A through C is: G1: =SUMPRODUCT(ISNUMBER(SEARCH("Y",A1:A6&B1:B6&C1:C6) )*D1:D6) Does that help? *********** Regards, Ron XL2002, WinXP-Pro "flow23" wrote: how do I sum the following if there is "Y" in any of the first 3 columns then take the value in 4th column, go to next row and if it contains "Y" in any of the first 3 columns then take the value. and so on.. and give a total of all rows taht contains "Y" . N N N 369192 44258 N N Y 53660 34229 N Y N 150273 12998 N Y Y 1191 423 Y N N 22245 2184 Y N Y 99 36 |
sum conditional
Thats fab thanks very much & "B. R.Ramachandran" wrote: Hi, One way: =SUMPRODUCT(--((A1:A6="Y")+(B1:B6="Y")+(C1:C6="Y")0)*D1:D6) Hope this helps. Regards, B. R. Ramachandran "flow23" wrote: how do I sum the following if there is "Y" in any of the first 3 columns then take the value in 4th column, go to next row and if it contains "Y" in any of the first 3 columns then take the value. and so on.. and give a total of all rows taht contains "Y" . N N N 369192 44258 N N Y 53660 34229 N Y N 150273 12998 N Y Y 1191 423 Y N N 22245 2184 Y N Y 99 36 |
| All times are GMT +1. The time now is 04:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com