Sum every other row in a range if value of every other cell is greater than 0

[emm8080]

Hi
I want to sum the values of every other cell in a range, but only if all the cells in the every other cell range have a value greater than 0, eg.

A B
1 3
4 2
4 0
2 1

In column A, both the first and third cells contain a value greater than 0, so the value returned would be 5.
In column B, the first cell contains a value greater than 0, but the third cell does not, so the value returned would be "test not completed".

I have this so far....

Please Login or Register  to view this content.

but this checks every cell in the range is >0 and not every other.

Thanks

[martindwilson]

MOD(ROW(),2) =1 for odd rows 0 for even rows
so
=IF(SUMPRODUCT((MOD(ROW(A1:A10),2)=1)*(A1:A10=0)),"not complete",SUMPRODUCT((MOD(ROW(A1:A10),2)=1)*(A1:A10)))
no blanks allowed unless you intentionally want them because blank =0
mind you to ignore blanks
=IF(SUMPRODUCT((MOD(ROW(A1:A10),2)=1)*(A1:A10=0)*(A1:A10<>"")),"not complete",SUMPRODUCT((MOD(ROW(A1:A10),2)=1)*(A1:A10)))

[npamcpp]

Or maybe

Please Login or Register  to view this content.

[emm8080]

Thanks Martin, that's great!

I was forgetting about blanks (of which there are a few) - how can I treat blanks the same as 0 ie blanks would return the value "not completed" rather than being ignored?
Regards
Emma

[martindwilson]

use the first formula as i said it counts blanks as zero
=IF(SUMPRODUCT((MOD(ROW(A1:A10),2)=1)*(A1:A10=0)),"not complete",SUMPRODUCT((MOD(ROW(A1:A10),2)=1)*(A1:A10)))

[emm8080]

many thanks!

[jason.b75]

You don't say which version of excel you're using, with 2007 or later

=IFERROR(SUM(IF(MOD(ROW(C$11:C$89),2),1/(1/C$11:C$89))),"not complete")

Which must be array confirmed using Shift Ctrl Enter.

[Cutter]

@emm8080

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save