SUMIFS (text to number problem)

Hi all,

Hope all is well. I need some help on sumifs formula. I am trying to do a sumifs formula however I have the criteria range which is in text. Therefore the sumif formula is not working properly. Is there a possible way how I can incorporate this fact into the formula so as to convert the text to number, in order for the sumif formula to work?

Apologies, if this thread has been posted already, if yes can someone pls guide me accordingly?

I have also attached the file for reference

Thanks everyone

Hi,

Can you not add a helper column to convert the text into numbers?

=C9*1

See the attached file.

I believe that this will do what you want.

Copy of Test(2).xlsx

Hope this helps.

Hi,

You can change all the text values by selecting the range (C9:C28) using Data | Text-to-columns then clicking 'Finish' on the first dialogue box. That will make them all numbers so the formula will work.

If the data is being imported from elsewhere as text or there's some other reason it needs to be text, then try using VALUE. =VALUE(A1) will return the numerical value of A1, even if it's formatted as text. Unfortunately, I'm on 2003 at work, which doesn't support SUMIFS so I can't check exactly how this will fit into your formula - but it should be something like:

Formula:

Please Login or Register  to view this content.

You'll need to enter it as an array formula by pressing Ctrl-Shift-Enter instead of just Enter - the formula will then get curly brackets {} around it. If you press just Enter, you'll get an error or an obviously wrong result - go back into the cell, press F2 then Ctrl-Shift-Enter again.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

=sumproduct((e9:e28)*(c9:c28>="20500000")*(c9:c28<="2100000"))

As I said above, I can't check the SUMIFS formula at the moment, but this Excel2003 equivalent works:

Formula:

Please Login or Register  to view this content.

(again, an array formula - enter with Ctrl-Shift-Enter)
so I don't see why the SUMIFS I suggested above shouldn't.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

Hi all,

CbaTrody and JeteMc thanks a lot for your reply, both your replies provided a solution, so thanks I was also interested in seeing whether I could obtain the same result without adding a helper column!

Ardigspook the first solution you provided uncluckily did not work (the one with the array), however the second one worked magic! So thanks a lot

JohnTopley also thanks a lot, your solution also worked wonders!!

Cheers guys!

@Keibri

You're welcome.

@AlKey

I didn't know that - thanks.

Regards,
Aardigspook