SUMIFS returning #VALUE

Hello,

I am attempting to do a sum with multiple criteria. I researched, input my formula and got #VALUE. I tried the wizard, got the same formula and got the same results.

My formula is

=SUMIFS(Tokens!F2:F10,Tokens!A2:A10,A16,Tokens!G2:G11,Tokens!I1)

I believe my problem is the range Tokens!F2:F10 is a VLOOKUP that returns a number, but are not a number originally.

As I am not fluent in Excel I am probably going the long way around to get my results. I have attached a sample spreadsheet for demonstration.



Entries into the file will be logged on the Tokens tab and and calculated on the Overview tab. What I am attempting to do is make a formula that will deduct the points employees Redeem. This will be displayed in the Balance column of Overview. This will be tracked on Tokens tab in the Redeemed column.

I am able to Count the points properly, so what I am trying to do is IF Redeemed = YES then Count Points to tally the points they redeemed

I was then going to expand on that formula to do something like =SUM(Points-Redeemed) which would give me the balance.

There is probably a better approach to this.

Any help would be appreciated.

Thanks!




Q2 cert Tokens.xlsx

=SUMIFS(Tokens!F2:F10,Tokens!A2:A10,A16,Tokens!G2:G11,Tokens!I1)
needs to be
Tokens!G2:G10,
all the ranges need to be the same size
if the vlookup is returning a text number
change it to
=--vlookup(blah,range,2,false) or
=vlookup(blah,range,2,false))+0 or
=vlookup(blah,range,2,false)*1

try this:

Please Login or Register  to view this content.

Array formula, use ctrl+shift+enter

Fluent in Excel, LOL - that's a great way to put it

Your problem is that the SUMIFS criteria ranges are different sizes - change them to match each other, and your #VALUE goes away,

Hope this helps.


EDIT: I took what I thought was a quick phonecall after starting my answer - I see you've since solved this.