| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
| Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
| Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
| Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Linear Mode
