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

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

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

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

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

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