0

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?

JvdV
  • 70,606
  • 8
  • 39
  • 70
niuniu
  • 13
  • 3
  • I found an old post showing how here: https://exceljet.net/formula/sumifs-with-multiple-criteria-and-or-logic... you give the SUMIFS an array, then wrap it in sum: =SUM(SUMIFS(sum_range,criteria_range,{"01","02", "03"})) – mgrollins Jul 13 '19 at 00:20

2 Answers2

1

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

JvdV
  • 70,606
  • 8
  • 39
  • 70
0

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 :)

Terry W
  • 3,199
  • 2
  • 8
  • 24