count if
 

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

count if
 

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

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


--
Biff
Microsoft Excel MVP


"MrSpock" wrote in message
...
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

count if
 

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" wrote:

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

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


--
Biff
Microsoft Excel MVP


"MrSpock" wrote in message
...
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

count if
 

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

F22:

=--(E221000)

Then on your summary sheet:

=SUM('1:31'!F22)


--
Biff
Microsoft Excel MVP


"MrSpock" wrote in message
...
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" wrote:

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

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


--
Biff
Microsoft Excel MVP


"MrSpock" wrote in message
...
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

count if
 

Thanks for all your time and help my friend.

"T. Valko" wrote:

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

F22:

=--(E221000)

Then on your summary sheet:

=SUM('1:31'!F22)


--
Biff
Microsoft Excel MVP


"MrSpock" wrote in message
...
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" wrote:

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

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


--
Biff
Microsoft Excel MVP


"MrSpock" wrote in message
...
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

count if
 

You're welcome!

--
Biff
Microsoft Excel MVP


"MrSpock" wrote in message
...
Thanks for all your time and help my friend.

"T. Valko" wrote:

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

F22:

=--(E221000)

Then on your summary sheet:

=SUM('1:31'!F22)


--
Biff
Microsoft Excel MVP


"MrSpock" wrote in message
...
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" wrote:

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

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


--
Biff
Microsoft Excel MVP


"MrSpock" wrote in message
...
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