Countif and Sumif with refrence value a Cell on another Sheet in theWorkbook

  • Thread starter Thread starter Mike 215
  • Start date Start date
M

Mike 215

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
 
Try it like this:

=COUNTIF(G7:G16,">"&Sheet1!A3)
=SUMIF(G7:G17,">"&Sheet1!A3)

--
Biff
Microsoft Excel MVP










- Show quoted text -
Click to expand...

These formulas produced 0 value for both Array and Regular Formula.

I am using Excel 2003.
MIke
 
F40 =COUNTIF(G7:G16,">2000")
G40 =SUMIF(G7:G17,">2000")
Click to expand...

If above formulas worked then these formulas have to work:

=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


Try it like this:

=COUNTIF(G7:G16,">"&Sheet1!A3)
=SUMIF(G7:G17,">"&Sheet1!A3)

--
Biff
Microsoft Excel MVP










- Show quoted text -
Click to expand...

These formulas produced 0 value for both Array and Regular Formula.

I am using Excel 2003.
MIke
 
Back
Top