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:"&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))

--
Regards,

Peo Sjoblom


"mmock" <[email protected]> wrote in message
news:[email protected]...
>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:"&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))
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "mmock" <[email protected]> wrote in message
> news:[email protected]...
> >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,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

"Peo Sjoblom" wrote:

> =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))
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "mmock" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
> news:[email protected]...
> >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!
> >
>
>
>