| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif and Sumif with refrence value a Cell on another Sheet in theWorkbook
Hi.
I have a workbook for sales information. 'Sheet1'!A3 I want to put a number (2000) to compare Sheets 2 - 26 G2:G30 is the information I want to look at Sheets 2-26 F40 Counts the values in G2:G30 that are Greater Than 'Sheet1'!A3 Sheets 2-26 F41 Counts the values in G2:G30 that are Less Than 'Sheet1'!A3 Sheets 2-26 G40 Sums the values in G2:G30 that are Greater Than 'Sheet1'!A3 Sheets 2-26 G41 Sums the values in G2:G30 that are Less Than 'Sheet1'!A3 I can get the answers if I put in the 2000 into each of the following formulas, but is very laborsum to do it for 25 worksheets F40 =COUNTIF(G7:G16,"2000") and G40 =SUMIF(G7:G17,"2000") F41 =COUNTIF(G7:G16,"<2000") and G41 =SUMIF(G7:G17,"<2000") All help would be very appreciated Mike |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif and Sumif with refrence value a Cell on another Sheet in the Workbook
Try it like this:
=COUNTIF(G7:G16,""&Sheet1!A3) =SUMIF(G7:G17,""&Sheet1!A3) -- Biff Microsoft Excel MVP "Mike 215" wrote in message ... Hi. I have a workbook for sales information. 'Sheet1'!A3 I want to put a number (2000) to compare Sheets 2 - 26 G2:G30 is the information I want to look at Sheets 2-26 F40 Counts the values in G2:G30 that are Greater Than 'Sheet1'!A3 Sheets 2-26 F41 Counts the values in G2:G30 that are Less Than 'Sheet1'!A3 Sheets 2-26 G40 Sums the values in G2:G30 that are Greater Than 'Sheet1'!A3 Sheets 2-26 G41 Sums the values in G2:G30 that are Less Than 'Sheet1'!A3 I can get the answers if I put in the 2000 into each of the following formulas, but is very laborsum to do it for 25 worksheets F40 =COUNTIF(G7:G16,"2000") and G40 =SUMIF(G7:G17,"2000") F41 =COUNTIF(G7:G16,"<2000") and G41 =SUMIF(G7:G17,"<2000") All help would be very appreciated Mike |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif and Sumif with refrence value a Cell on another Sheet inthe Workbook
On Sep 2, 8:33*pm, "T. Valko" wrote:
Try it like this: =COUNTIF(G7:G16,""&Sheet1!A3) =SUMIF(G7:G17,""&Sheet1!A3) -- Biff Microsoft Excel MVP "Mike 215" wrote in message ... Hi. I have a workbook for sales information. 'Sheet1'!A3 * I want to put a number (2000) to compare Sheets 2 - 26 * G2:G30 * *is the information I want to look at Sheets 2-26 * * F40 * *Counts the values in G2:G30 that are Greater Than * 'Sheet1'!A3 Sheets 2-26 * * F41 * *Counts the values in G2:G30 that are Less Than * 'Sheet1'!A3 Sheets 2-26 * * G40 * *Sums the values in G2:G30 that are Greater Than * 'Sheet1'!A3 Sheets 2-26 * * G41 * *Sums the values in G2:G30 that are Less Than 'Sheet1'!A3 I can get the answers if I put in the 2000 into each of the following formulas, but is very laborsum to do it for 25 worksheets F40 *=COUNTIF(G7:G16,"2000") *and G40 *=SUMIF(G7:G17,"2000") F41 *=COUNTIF(G7:G16,"<2000") *and *G41 *=SUMIF(G7:G17,"<2000") All help would be very appreciated Mike- Hide quoted text - - Show quoted text - These formulas produced 0 value for both Array and Regular Formula. I am using Excel 2003. MIke |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif and Sumif with refrence value a Cell on another Sheet in the Workbook
F40 =COUNTIF(G7:G16,"2000")
G40 =SUMIF(G7:G17,"2000") If above formulas worked then these formulas have to work: 'Sheet1'!A3 I want to put a number (2000) =COUNTIF(G7:G16,""&Sheet1!A3) =SUMIF(G7:G17,""&Sheet1!A3) You're just replacing the hardcoded value 2000 with a cell reference. -- Biff Microsoft Excel MVP "Mike 215" wrote in message ... On Sep 2, 8:33 pm, "T. Valko" wrote: Try it like this: =COUNTIF(G7:G16,""&Sheet1!A3) =SUMIF(G7:G17,""&Sheet1!A3) -- Biff Microsoft Excel MVP "Mike 215" wrote in message ... Hi. I have a workbook for sales information. 'Sheet1'!A3 I want to put a number (2000) to compare Sheets 2 - 26 G2:G30 is the information I want to look at Sheets 2-26 F40 Counts the values in G2:G30 that are Greater Than 'Sheet1'!A3 Sheets 2-26 F41 Counts the values in G2:G30 that are Less Than 'Sheet1'!A3 Sheets 2-26 G40 Sums the values in G2:G30 that are Greater Than 'Sheet1'!A3 Sheets 2-26 G41 Sums the values in G2:G30 that are Less Than 'Sheet1'!A3 I can get the answers if I put in the 2000 into each of the following formulas, but is very laborsum to do it for 25 worksheets F40 =COUNTIF(G7:G16,"2000") and G40 =SUMIF(G7:G17,"2000") F41 =COUNTIF(G7:G16,"<2000") and G41 =SUMIF(G7:G17,"<2000") All help would be very appreciated Mike- Hide quoted text - - Show quoted text - These formulas produced 0 value for both Array and Regular Formula. I am using Excel 2003. MIke |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumif / Countif - Not certain - want to extract data from cell and | Excel Worksheet Functions | |||
| range,cell refrence | Excel Worksheet Functions | |||
| COUNTIF - SUMIF don't appear to work in this sheet. | New Users to Excel | |||
| How NOT to extend formula/refrence to a cell | Excel Worksheet Functions | |||
| Wildcards in other workbook cell refrence? | Excel Worksheet Functions | |||
Linear Mode
