Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
smilingindigo
 
Posts: n/a
Default Using SUMIF Function with a named cell reference as value in CRITE

I am trying to use a literal cell reference as a part of the criteria in the
SUMIF function, and i can't get it to sum properly. It only seems to like an
actual value, not a cell reference, to work properly. Does anyone know a
roundabout for this?

Sample table:

A B C
1 2
2 100 5
3 15 5
4 10 4
5 5 4
6 15 3
7 10 3
8 5 2
9 25 2
10 10 1
11 3 0

SUMIF(B2:B11,"=2",A2:A11) works fine
SUMIF(B2:B11,"=$C$1",A2:All) doesn't work, returns a value of 0 in another
spreadsheet (not the same cell values as the example, but the same
structure). I want to use C1 so i can change the criteria in the SUMIF
without having to change each and every reiteration of the SUMIF i have in
different subsections of the worksheet. C1 can equal 0, 1, 2, 3, 4, 5.

any recommendations? have you run into this issue before?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default Using SUMIF Function with a named cell reference as value in CRITE

Try =SUMIF(B2:B11,"="&$C$1,A2:A11)
With the $C$1 in quotes, it's treated as a literal, but you need to have the
cell reference evaluated.

"smilingindigo" wrote:

I am trying to use a literal cell reference as a part of the criteria in the
SUMIF function, and i can't get it to sum properly. It only seems to like an
actual value, not a cell reference, to work properly. Does anyone know a
roundabout for this?

Sample table:

A B C
1 2
2 100 5
3 15 5
4 10 4
5 5 4
6 15 3
7 10 3
8 5 2
9 25 2
10 10 1
11 3 0

SUMIF(B2:B11,"=2",A2:A11) works fine
SUMIF(B2:B11,"=$C$1",A2:All) doesn't work, returns a value of 0 in another
spreadsheet (not the same cell values as the example, but the same
structure). I want to use C1 so i can change the criteria in the SUMIF
without having to change each and every reiteration of the SUMIF i have in
different subsections of the worksheet. C1 can equal 0, 1, 2, 3, 4, 5.

any recommendations? have you run into this issue before?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Using SUMIF Function with a named cell reference as value in CRITE

Hi!

Try it this way:

=SUMIF(B2:B11,"="&$C$1,A2:A11)

Biff

"smilingindigo" wrote in message
...
I am trying to use a literal cell reference as a part of the criteria in
the
SUMIF function, and i can't get it to sum properly. It only seems to like
an
actual value, not a cell reference, to work properly. Does anyone know a
roundabout for this?

Sample table:

A B C
1 2
2 100 5
3 15 5
4 10 4
5 5 4
6 15 3
7 10 3
8 5 2
9 25 2
10 10 1
11 3 0

SUMIF(B2:B11,"=2",A2:A11) works fine
SUMIF(B2:B11,"=$C$1",A2:All) doesn't work, returns a value of 0 in
another
spreadsheet (not the same cell values as the example, but the same
structure). I want to use C1 so i can change the criteria in the SUMIF
without having to change each and every reiteration of the SUMIF i have in
different subsections of the worksheet. C1 can equal 0, 1, 2, 3, 4, 5.

any recommendations? have you run into this issue before?



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Using SUMIF Function with a named cell reference as value in CRITE

Try it as: =SUMIF(B2:B11,"="&$C$1,A2:A11)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"smilingindigo" wrote:
I am trying to use a literal cell reference as a part of the criteria in the
SUMIF function, and i can't get it to sum properly. It only seems to like an
actual value, not a cell reference, to work properly. Does anyone know a
roundabout for this?

Sample table:

A B C
1 2
2 100 5
3 15 5
4 10 4
5 5 4
6 15 3
7 10 3
8 5 2
9 25 2
10 10 1
11 3 0

SUMIF(B2:B11,"=2",A2:A11) works fine
SUMIF(B2:B11,"=$C$1",A2:All) doesn't work, returns a value of 0 in another
spreadsheet (not the same cell values as the example, but the same
structure). I want to use C1 so i can change the criteria in the SUMIF
without having to change each and every reiteration of the SUMIF i have in
different subsections of the worksheet. C1 can equal 0, 1, 2, 3, 4, 5.

any recommendations? have you run into this issue before?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple cell reference IF function Colinhp Excel Worksheet Functions 7 June 21st 06 09:09 PM
Custom functions calculating time arguments Help Desperate Bill_De Excel Worksheet Functions 12 April 25th 06 02:22 AM
Cell Reference in Function astrodon Excel Worksheet Functions 4 February 19th 06 07:47 PM
Offset Function works in cell, not in named range DragonslayerApps Excel Worksheet Functions 0 July 25th 05 04:39 PM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM


All times are GMT +1. The time now is 02:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"