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:

=SUM(IF(MOD(A1:A5,2),A1:C5))

For Count

Formula:

=COUNT(IF(MOD(ROW(A1:C5),2),A1:C5))

@ 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...

=SUM(IF(MOD(A1:A6,2)=1,--ISNUMBER(A1:C6)))

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

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

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

It's enough to have:

Formula:

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

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

FD,
work for me.

@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 think 0 in the false condition is producing the error.

You formula could be like this.....

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

Or just leave the false condition,

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

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:

=SUMPRODUCT(NOT(ISBLANK(A1:C6))* ISODD(ROW(A1:C6)))

I think it should be..
Count:-

Formula:

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

Sum:-

Formula:

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

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