count if

[MrSpock]

Hello, My workbook contains a worksheet for each day of the month for store
sales. What I want to do is count how many times sales are over a certain
amount for a store.
Example my chicago store daily sales are in cell e22 across worksheets 1
through 31, i want to count how many times the stores sales were over say
1000.
Sure would appreciate any help this is driving me crazy. LOL

Roger

 

[T. Valko]

Assuming your sheet names are 1, 2, 3, 4 .... 31

=SUMPRODUCT(COUNTIF(INDIRECT("'"&ROW(INDIRECT("1:31"))&"'!E22"),">1000"))

 

[MrSpock]

Thank You very much for your help. When I paste this into my workbook though
I get a #REF error. The sheet names are as you assume except that sundays are
not there such as 1,2,3,4,5,7,8,9,10,11,12,14 etc.

 

[T. Valko]

In that case I would just use an additional cell (the same cell on each
sheet) with a formula like this:

F22:

=--(E22>1000)

Then on your summary sheet:

=SUM('1:31'!F22)

 

[MrSpock]

Thanks for all your time and help my friend.

In that case I would just use an additional cell (the same cell on each
sheet) with a formula like this:

F22:

=--(E22>1000)

Then on your summary sheet:

=SUM('1:31'!F22)

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks for all your time and help my friend.