Can you SUMIF using a cell that is populated via data validation

[mrkris1982]

Column A of sheet 1 is populated with names.....via list validation
Column U of sheet 1 has a $ amount

Lets say in A2, i select the name Bob Jones.....fill out some other stuff and the total cost in U2 is $400

On sheet 3, B2, I have this formula...trying to capture the total number of dollars that are associated with Bob Jones:

=SUMIF('sheet1'!A:A,"*Jones*",'sheet1'!U:U)

The result is #REF but not sure why - making the assumption its due to the list validation. Not sure if this can be confirmed, but wondering how I can work around this while keeping the validation in place.

 

[Coding4Fun]

No it has nothing to do with the Validation List being used within the cell, I am able to use SumIf with a column populated with data from a Validation List. I think you have a syntax error on your sheet name, I see added single quotes and I am not sure if sheet names are case sensitive or not but your using sheet1 vs Sheet1

Correct Way

=SUMIF(Sheet1!A:A,"*Jones*",Sheet1!U:U)

Incorrect - Note the single quotes, those are used in VBA code not in formulas in a cell.

=SUMIF([COLOR=#ff0000]'[/COLOR]Sheet1[COLOR=#ff0000]'[/COLOR]!A:A,"*Jones*",[COLOR=#ff0000]'[/COLOR]Sheet1[COLOR=#ff0000]'[/COLOR]!U:U)

I don't get a #REF error but it just returns 0 if I type it the way you have it.

 

[Fluff]

I suspect that in col U on sheet1 you have #REF ! values.

 

[Coding4Fun]

I suspect that in col U on sheet1 you have #REF ! values.

Good Call