Counting spefic cell (not a range) with a value greater than 1
 

I am looking for a formula to count specific cells and a range (A5, A10, A15,
A20 and so on) but only count when the value is greater than $1.00. So if
the value of cell A5 was $1.50, the value of cell A10 was $1.25, the value of
cell A15 was $0.50 and the value of cell A20 was $2.00 the total count would
be 3. I'm stumped!

=SUMPRODUCT(--(LARGE((A5,A10,A15,A20,A25),ROW(INDIRECT("1:"&COUN T(A5,A10,A15,A20,A25))))1))


however if you want to sum every 5th cell greater than 1 you can use


=SUMPRODUCT(--(MOD(ROW(A5:A50),5)=0),--(A5:A501))

--
Regards,

Peo Sjoblom


"mmock" wrote in message
...
I am looking for a formula to count specific cells and a range (A5, A10,
A15,
A20 and so on) but only count when the value is greater than $1.00. So if
the value of cell A5 was $1.50, the value of cell A10 was $1.25, the value
of
cell A15 was $0.50 and the value of cell A20 was $2.00 the total count
would
be 3. I'm stumped!

I used every 5th cell as an example. I haven't tried this yet and do not
want the product of the cells just to know if a cell is got a value in it
greater than 1. I will try and get back to you....THANKS! and sorry for the
multipul posts.

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(LARGE((A5,A10,A15,A20,A25),ROW(INDIRECT("1:"&COUN T(A5,A10,A15,A20,A25))))1))


however if you want to sum every 5th cell greater than 1 you can use


=SUMPRODUCT(--(MOD(ROW(A5:A50),5)=0),--(A5:A501))

--
Regards,

Peo Sjoblom


"mmock" wrote in message
...
I am looking for a formula to count specific cells and a range (A5, A10,
A15,
A20 and so on) but only count when the value is greater than $1.00. So if
the value of cell A5 was $1.50, the value of cell A10 was $1.25, the value
of
cell A15 was $0.50 and the value of cell A20 was $2.00 the total count
would
be 3. I'm stumped!

Thank you....it did work; however, there is one problem. I have 34 cells
that need to be counted and it looks like I can only get 30. I am going to
list the cells to see if you can assist again.
K17,K27,K37,K47,K57,K67,K77,K87,K102,K112,K122,K13 2,K147,K157,K172,K182,K197,K207,K217,K227,K237,K24 7,K257,K267,K277,K287,K297,K307,K322,K332,K347,K35 7,K367,K377

thanks,M

"Peo Sjoblom" wrote:

=SUMPRODUCT(--(LARGE((A5,A10,A15,A20,A25),ROW(INDIRECT("1:"&COUN T(A5,A10,A15,A20,A25))))1))


however if you want to sum every 5th cell greater than 1 you can use


=SUMPRODUCT(--(MOD(ROW(A5:A50),5)=0),--(A5:A501))

--
Regards,

Peo Sjoblom


"mmock" wrote in message
...
I am looking for a formula to count specific cells and a range (A5, A10,
A15,
A20 and so on) but only count when the value is greater than $1.00. So if
the value of cell A5 was $1.50, the value of cell A10 was $1.25, the value
of
cell A15 was $0.50 and the value of cell A20 was $2.00 the total count
would
be 3. I'm stumped!

I believe you can put half the cells in one formula and repeat the formula
with the second half of the cells and put a + between the two formulas.
Something like this.
I only used 5 cells but you would use about 17 in each formula. I did not
test it with 17 cells.

=SUMPRODUCT(--(LARGE((A1,A2,A3,A4,A5),ROW(INDIRECT("1:"&COUNT(A1 ,A2,A3,A4,A5))))1))+SUMPRODUCT(--(LARGE((B1,B2,B3,B4,B5),ROW(INDIRECT("1:"&COUNT(B1 ,B2,B3,B4,B5))))1))

HTH
Regards,
Howard

"mmock" wrote in message
...
I am looking for a formula to count specific cells and a range (A5, A10,
A15,
A20 and so on) but only count when the value is greater than $1.00. So if
the value of cell A5 was $1.50, the value of cell A10 was $1.25, the value
of
cell A15 was $0.50 and the value of cell A20 was $2.00 the total count
would
be 3. I'm stumped!

Thank you...IT WORKED!!!

"L. Howard Kittle" wrote:

I believe you can put half the cells in one formula and repeat the formula
with the second half of the cells and put a + between the two formulas.
Something like this.
I only used 5 cells but you would use about 17 in each formula. I did not
test it with 17 cells.

=SUMPRODUCT(--(LARGE((A1,A2,A3,A4,A5),ROW(INDIRECT("1:"&COUNT(A1 ,A2,A3,A4,A5))))1))+SUMPRODUCT(--(LARGE((B1,B2,B3,B4,B5),ROW(INDIRECT("1:"&COUNT(B1 ,B2,B3,B4,B5))))1))

HTH
Regards,
Howard

"mmock" wrote in message
...
I am looking for a formula to count specific cells and a range (A5, A10,
A15,
A20 and so on) but only count when the value is greater than $1.00. So if
the value of cell A5 was $1.50, the value of cell A10 was $1.25, the value
of
cell A15 was $0.50 and the value of cell A20 was $2.00 the total count
would
be 3. I'm stumped!