i have a large spreadsheet where i have to check each 3rd cell to see if it
is greater than 0. if it is, then i have to take the 2nd cell and add it to
others where the cell is greater than 0. If the 3rd cell is 0, then the 2nd
cell is ignored.



Laurie

How about posting a small example and tell us what result you expect. Also,
tell us where this data is!

--
Biff
Microsoft Excel MVP


"Laurie" wrote:

i have a large spreadsheet where i have to check each 3rd cell to see if it
is greater than 0. if it is, then i have to take the 2nd cell and add it to
others where the cell is greater than 0. If the 3rd cell is 0, then the 2nd
cell is ignored.



Laurie

Is there an easy way to post a small example. I have one ready, but it
doesn't look very readable when I paste it.

A B C D E F G H
2 100 a 40 e 3 5
if 2nd cell 0 add cell befor itl to total
if 2nd cell <1 ignore and do not add cell to total
answer row 1 5

"T. Valko" wrote:

How about posting a small example and tell us what result you expect. Also,
tell us where this data is!

--
Biff
Microsoft Excel MVP


"Laurie" wrote:

i have a large spreadsheet where i have to check each 3rd cell to see if it
is greater than 0. if it is, then i have to take the 2nd cell and add it to
others where the cell is greater than 0. If the 3rd cell is 0, then the 2nd
cell is ignored.



Laurie

Try this:

=SUMIF(B2:G2,"0",A2:F2)

--
Biff
Microsoft Excel MVP


"Laurie" wrote:

Is there an easy way to post a small example. I have one ready, but it
doesn't look very readable when I paste it.

A B C D E F G H
2 100 a 40 e 3 5
if 2nd cell 0 add cell befor itl to total
if 2nd cell <1 ignore and do not add cell to total
answer row 1 5

"T. Valko" wrote:

How about posting a small example and tell us what result you expect. Also,
tell us where this data is!

--
Biff
Microsoft Excel MVP


"Laurie" wrote:

i have a large spreadsheet where i have to check each 3rd cell to see if it
is greater than 0. if it is, then i have to take the 2nd cell and add it to
others where the cell is greater than 0. If the 3rd cell is 0, then the 2nd
cell is ignored.



Laurie

Hi,

I'm confused, your title says every 3rd number but your second post says
every 2nd number? Here is the solution for you second post, ie. check every
2nd number, if 0 add the number to the left of it. I chose to do this one
because your sample data matches it.

Supppose you data is in the range A2:H2 the following array formula returns
the result you show:

=SUM(MOD(COLUMN(A2:H2),2)*IF(ISNUMBER(B2:I2),IF(B2 :I20,1,0),0)*IF(ISNUMBER(A2:H2),A2:H2,0))

to make it an array you must enter it by pressing Shift+Ctrl+Enter, not Enter.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Laurie" wrote:

i have a large spreadsheet where i have to check each 3rd cell to see if it
is greater than 0. if it is, then i have to take the 2nd cell and add it to
others where the cell is greater than 0. If the 3rd cell is 0, then the 2nd
cell is ignored.



Laurie

Hi,

Your formula returns the correct result if you change it to read
=SUMIF(B2:H2,"0",A2:F2)
but it isn't handling the every second cell 0 conditions. For example, if
E2 contained 1 your formula would return 45 but the answer should still be 5,
because E2 is not one of the every second cells.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"T. Valko" wrote:

Try this:

=SUMIF(B2:G2,"0",A2:F2)

--
Biff
Microsoft Excel MVP


"Laurie" wrote:

Is there an easy way to post a small example. I have one ready, but it
doesn't look very readable when I paste it.

A B C D E F G H
2 100 a 40 e 3 5
if 2nd cell 0 add cell befor itl to total
if 2nd cell <1 ignore and do not add cell to total
answer row 1 5

"T. Valko" wrote:

How about posting a small example and tell us what result you expect. Also,
tell us where this data is!

--
Biff
Microsoft Excel MVP


"Laurie" wrote:

i have a large spreadsheet where i have to check each 3rd cell to see if it
is greater than 0. if it is, then i have to take the 2nd cell and add it to
others where the cell is greater than 0. If the 3rd cell is 0, then the 2nd
cell is ignored.



Laurie

Now that I've revisited this I'm confused as to what the OP really means/wants.

They say the result they expect is 5 and they show a 5 as the last entry in
the sample.

Is the 5 they show in the sample the *result* of the desired formula or is
the 5 part of the data?

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote:

Hi,

Your formula returns the correct result if you change it to read
=SUMIF(B2:H2,"0",A2:F2)
but it isn't handling the every second cell 0 conditions. For example, if
E2 contained 1 your formula would return 45 but the answer should still be 5,
because E2 is not one of the every second cells.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"T. Valko" wrote:

Try this:

=SUMIF(B2:G2,"0",A2:F2)

--
Biff
Microsoft Excel MVP


"Laurie" wrote:

Is there an easy way to post a small example. I have one ready, but it
doesn't look very readable when I paste it.

A B C D E F G H
2 100 a 40 e 3 5
if 2nd cell 0 add cell befor itl to total
if 2nd cell <1 ignore and do not add cell to total
answer row 1 5

"T. Valko" wrote:

How about posting a small example and tell us what result you expect. Also,
tell us where this data is!

--
Biff
Microsoft Excel MVP


"Laurie" wrote:

i have a large spreadsheet where i have to check each 3rd cell to see if it
is greater than 0. if it is, then i have to take the 2nd cell and add it to
others where the cell is greater than 0. If the 3rd cell is 0, then the 2nd
cell is ignored.



Laurie

With that formula I get a result of 2. The OP says they expect a result of 5.

I think we need a better explanation.

The Op posted a sample range that spans 8 columns and then shows only 7 bits
of sample data. As I noted in my other reply, is the 5 a data bit or is it
the desired result of the formula?

To the OP....

You need to be very specific with your explanation. What you want to do is
basically easy but as you can see, when we have to "guess what you want" we
can really butcher a very simple application!


--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote:

Hi,

I'm confused, your title says every 3rd number but your second post says
every 2nd number? Here is the solution for you second post, ie. check every
2nd number, if 0 add the number to the left of it. I chose to do this one
because your sample data matches it.

Supppose you data is in the range A2:H2 the following array formula returns
the result you show:

=SUM(MOD(COLUMN(A2:H2),2)*IF(ISNUMBER(B2:I2),IF(B2 :I20,1,0),0)*IF(ISNUMBER(A2:H2),A2:H2,0))

to make it an array you must enter it by pressing Shift+Ctrl+Enter, not Enter.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Laurie" wrote:

i have a large spreadsheet where i have to check each 3rd cell to see if it
is greater than 0. if it is, then i have to take the 2nd cell and add it to
others where the cell is greater than 0. If the 3rd cell is 0, then the 2nd
cell is ignored.



Laurie

Hi Biff,

As I hope the OP can see we are all a little confused on this one.

I did get the result of 5 using my formula and the OP's data, but my formula
is for every 2nd cell0 although it can be easily modified to handle every
3rd cell0.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"T. Valko" wrote:

With that formula I get a result of 2. The OP says they expect a result of 5.

I think we need a better explanation.

The Op posted a sample range that spans 8 columns and then shows only 7 bits
of sample data. As I noted in my other reply, is the 5 a data bit or is it
the desired result of the formula?

To the OP....

You need to be very specific with your explanation. What you want to do is
basically easy but as you can see, when we have to "guess what you want" we
can really butcher a very simple application!


--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote:

Hi,

I'm confused, your title says every 3rd number but your second post says
every 2nd number? Here is the solution for you second post, ie. check every
2nd number, if 0 add the number to the left of it. I chose to do this one
because your sample data matches it.

Supppose you data is in the range A2:H2 the following array formula returns
the result you show:

=SUM(MOD(COLUMN(A2:H2),2)*IF(ISNUMBER(B2:I2),IF(B2 :I20,1,0),0)*IF(ISNUMBER(A2:H2),A2:H2,0))

to make it an array you must enter it by pressing Shift+Ctrl+Enter, not Enter.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Laurie" wrote:

i have a large spreadsheet where i have to check each 3rd cell to see if it
is greater than 0. if it is, then i have to take the 2nd cell and add it to
others where the cell is greater than 0. If the 3rd cell is 0, then the 2nd
cell is ignored.



Laurie