SUM array works, COUNT array does not?

In an array like this...

	A	B	C
2	1	2	4
3	2	4	8
4	3	6	12
5	4	8	16
6	5	10	20

I am trying to sum and count numbers in every 2nd row.

I am using these ARRAY's...
=SUM(IF(MOD(A1:A6,2)=1,A1:C6,0))
works fine, gives 63

and then...
=COUNT(IF(MOD(A1:A6,2)=1,A1:C6,0))
doesnt work, gives 18

What gives, what am I missing?

Edit: It seems that it is doubling the count...6 rows using every 2nd row = 3 rows with 3 values in each = 9 numbers, not 18

Why using = 1...
I believe there is no need...
For Sum

Formula:

Please Login or Register  to view this content.

For Count

Formula:

Please Login or Register  to view this content.

@ Vikas, yes, that works for this situation there are only 2 options - 0 or 1

and now Im even more confused (maybe coz its late here, 20 past midnite), but deleting ALL values does not change the answers...

	A	B	C	D	E
1				9	=COUNT(IF(MOD(ROW(A1:A6),2)=1,A1:C6))
2	1	2	4	9	=COUNT(IF(MOD(ROW(A1:A6),2)=1,A1:C6))
3	2	4	8	9	=COUNT(IF(MOD(ROW(A1:C5),2),A1:C5))
4	3	6	12
5	4	8	16
6	5	10	20

and...

	A	B	C	D	E
1				9	=COUNT(IF(MOD(ROW(A1:A6),2)=1,A1:C6))
2				9	=COUNT(IF(MOD(ROW(A1:A6),2)=1,A1:C6))
3				9	=COUNT(IF(MOD(ROW(A1:C5),2),A1:C5))
4
5
6

I think to count numbers as per the criteria, the following Array Formula can be used...

Please Login or Register  to view this content.

Or this non-array formula......

Please Login or Register  to view this content.

sktneer,
no need to convert TRUE/FALSE to 1/0 with -- AND to multiply.

It's enough to have:

Formula:

Please Login or Register  to view this content.

Any math operation will convert TRUE/FALSE into numbers (including *)

FD,
work for me.

I think 0 in the false condition is producing the error.

You formula could be like this.....

Please Login or Register  to view this content.

Or just leave the false condition,

Please Login or Register  to view this content.

@zbor
You are correct. No need to use double negative in this case.
And You already suggested the formula in post#7 what I suggested in post#8. Didn't see it.

I've just use FDibbins's formulae as they are.
I didn't change them and I got correct result.

@FDibbins, check did you accidently use manual calculation in workbook.

No Sir. The formula you used is different from one which Ford used originally. You removed the 0 from the formula as a false condition while Ford used 0 in his formula (Post#1). Count() will treat 0 a a number.

I'm talking about FD formulae in #4, not #1.

Oh yes. He also used the same formula in post#4. In this case what you suggested about the manual calculation might be the reason behind the wrong output.

This will work for counting values in every odd row.

Formula:

Please Login or Register  to view this content.

I think it should be..
Count:-

Formula:

Please Login or Register  to view this content.

Sum:-

Formula:

Please Login or Register  to view this content.

Check the attached file..

Only have a few minutes, but no, calcs were not set to manual. I will look again in an hour or so, thanks for all the inputs guys

I think there are 2 separate issues that have arisen:

Firstly when you use this version

=COUNT(IF(MOD(ROW(A1:C6),2)=1,A1:C6,0))

You get 18 because you have zero as the FALSE argument, and COUNT counts those zeroes.......but suggested fix, i.e.

=COUNT(IF(MOD(ROW(A1:C6),2)=1,A1:C6))

will still count blanks in A1:C6 as zeroes and so COUNT counts those too, giving 9 even if A1:C6 is completely blank

To me there is also a "robustness" issue using ROW(A1:C6) in that you will get different results if you insert a row at row 1 - for that reason my suggested solution is this:

=SUMPRODUCT(ISNUMBER(A1:C6)*(MOD(ROW(A1:C6)-ROW(A1),2)=1))

That would fix all the issues with a robust non array formula

DDL thanks for that, it was driving me nuts