|
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 12:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com