make criteria in sumif refer to another cell
 

I have a sumif function to return a report from a worksheet and would like to
make the condition refer to another cell (in the same workbook).

E.g.

This works...

A2: =SUMIF('Shipped Product'!A5:A100,"CustomerXYZ",'Shipped
Product'!E5:E100)

But this doesn't...

A1: CustomerXYZ
A2: =SUMIF('Shipped Product'!A5:A100,"=A1",'Shipped Product'!E5:E100)

How do I make the condition be that the range has to match what is in cell A1?

Thanks for your help.

make criteria in sumif refer to another cell
 

Hi Keith,

With "=A1", it's looking for a cell that literally has "=A1" in the cell
value. You should try "="&A1 in place of "=A1". Hope this helps.

-Simon

"keithjdowling" wrote:

I have a sumif function to return a report from a worksheet and would like to
make the condition refer to another cell (in the same workbook).

E.g.

This works...

A2: =SUMIF('Shipped Product'!A5:A100,"CustomerXYZ",'Shipped
Product'!E5:E100)

But this doesn't...

A1: CustomerXYZ
A2: =SUMIF('Shipped Product'!A5:A100,"=A1",'Shipped Product'!E5:E100)

How do I make the condition be that the range has to match what is in cell A1?

Thanks for your help.

make criteria in sumif refer to another cell
 

Or just A1 between the middle commas (no quotes).

Pete

make criteria in sumif refer to another cell
 

Thanks, it now works as I need!

"SiC" wrote:

Hi Keith,

With "=A1", it's looking for a cell that literally has "=A1" in the cell
value. You should try "="&A1 in place of "=A1". Hope this helps.

-Simon

"keithjdowling" wrote:

I have a sumif function to return a report from a worksheet and would like to
make the condition refer to another cell (in the same workbook).

E.g.

This works...

A2: =SUMIF('Shipped Product'!A5:A100,"CustomerXYZ",'Shipped
Product'!E5:E100)

But this doesn't...

A1: CustomerXYZ
A2: =SUMIF('Shipped Product'!A5:A100,"=A1",'Shipped Product'!E5:E100)

How do I make the condition be that the range has to match what is in cell A1?

Thanks for your help.

make criteria in sumif refer to another cell
 

Thanks, this works too (the sumif help file made it look like quotes were
mandatory - I guess I misunderstood)

"Pete_UK" wrote:

Or just A1 between the middle commas (no quotes).

Pete