SumIF but Ignore HREF! error

[movingpartz]

Im trying to create a formula that calculates the sum of a column (ignore #REF!)

The criteria is that Column C = "Gem 03" but ignore #Ref!.

Or

Another approach could be

Column C = "Gem 03" and Column D is = Diamond_Round


Ive attached the spreadsheet. The formula i tried is in C:33. I get #Value

=SUMIFS(Sheet1!E:E,Sheet1!C3:C502,"Gem 03",Sheet1!E:E,"<>#REF!")

I also tried this

=SUMIFS(Sheet1!E:E,Sheet1!C3:C502,"Gem 03",Sheet1!D:D,"Diamond_Round")

Any help is appreciated!

J

[FlameRetired]

Try removing your formula from column C. Then use this formula.

Please Login or Register  to view this content.

[FlameRetired]

Also try

Please Login or Register  to view this content.

[mrshl9898]

It didn't like the c range you selected as it didn't match the sum range

=SUMIFS(Sheet1!E:E,Sheet1!C:C,"Gem 03",Sheet1!E:E,"<>#REF!")

[FlameRetired]

Withdrawn by FR. Addressed post to wrong party.

[movingpartz]

I tried both formulas. I'm not sure what Im doing wrong. Now the result is 0

The result should be .266

[FlameRetired]

.266 is what I am getting. In what cell are you putting the formula? It cannot be within the range of sum_range or criteria_range. I put mine in F3.

[movingpartz]

I see what i did wrong.

Your formula totally worked. Thank you so much FlameRetired! Much appreciated.

Jill

[FlameRetired]

Good deal. You are welcome. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.