CODEWITHSUNDEEP
excelexcel-formulasumifs
niuniu
niuniu

Reputation: 13

Sumifs for filtering on text value of numbers

Trying to use SUMIFS from a data dump (i.e. can't manipulate the raw data). One of the criteria of the SUMIFS is to filter on the periods, where periods are in text value 01,02,03...10,11,12. I want to filter on such as "Period is less than 3". How would i do that?

Tried multiple ways such as SUMIFS(Range, Period, "<=03") and all sorts of variations but just won't work.

Can someone please help out?

Upvotes: 0

Views: 515

Answers (2)

Terry W
Terry W

Reputation: 3257

The answer from JvdV is definitely a good one but if you do want to use SUMIF you can try to add a helper column to the original data set to convert the periods from text to number using NUMBERVALUE function as shown in the picture below. Then your SUMIF function will work fine by referencing to the helper column for periods.

enter image description here

Cheers :)

Upvotes: 0

JvdV
JvdV

Reputation: 75850

I want to filter on such as "Period is less than 3". How would i do that?

=SUMPRODUCT(--(Range*1<3))

enter image description here

Upvotes: 1

Related Questions