sum conditional

[Guest]

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

 

[Guest]

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

Does that help?

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

XL2002, WinXP-Pro

 

[Guest]

Hi,

One way:

=SUMPRODUCT(--((A1:A6="Y")+(B1:B6="Y")+(C1:C6="Y")>0)*D16)

Hope this helps.

Regards,
B. R. Ramachandran

 

[Guest]

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:A34,raw_overview!B29:B34,raw_overview!C29:C34))*raw_overview!A29:A34)


anything wrong?

 

[Guest]

Try this:

=SUMPRODUCT(ISNUMBER(SEARCH("Y",raw_overview!A29:A34&raw_overview!B29:B34&raw_overview!C29:C34))*raw_overview!D2934)

Does that help?

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

XL2002, WinXP-Pro

 

[Guest]

Thats fab

thanks very much
&

Hi,

One way:

=SUMPRODUCT(--((A1:A6="Y")+(B1:B6="Y")+(C1:C6="Y")>0)*D16)

Hope this helps.

Regards,
B. R. Ramachandran