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

[Guest]

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!

 

[Peo Sjoblom]

=SUMPRODUCT(--(LARGE((A5,A10,A15,A20,A25),ROW(INDIRECT("1:"&COUNT(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:A50>1))

 

[Guest]

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.

 

[Guest]

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,K132,K147,K157,K172,K182,K197,K207,K217,K227,K237,K247,K257,K267,K277,K287,K297,K307,K322,K332,K347,K357,K367,K377

thanks,M

 

[L. Howard Kittle]

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

 

[Guest]

Thank you...IT WORKED!!!

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