Question regarding SUMIFS and value

[jasonbrisson]

Howdy all,

Quick background- I've got a large spreadsheet of Excel records that contain the sales amount, month sold, "Prime SKU Indicator" (not relevant what that means), the servicing location, and the qty sold for a list of items. All of these records are actually formulas that return a value based on some parameters in another sheet.

For example, instead of having the actual value of "August" or whatever appropriate month would be, what can be found in the month column is actually "=IF('RS6000 Report'!R2="","",TEXT('RS6000 Report'!R2,"mmmm"))". This statement will evaluate to a month (or a blank cell, depending on 'RS6000 Report'!R2) but in truth is actually a formula because I do not want to add value to this column.

Similar for the rest of the information contained in the records.

Now, here comes my challenge. I am trying to do a SUMIFS statement based on parameters for the sales amount, month sold, "Prime SKU Indicator", the servicing location, and the qty sold. For example: "=SUMIFS(R2:R100001,Q2:Q100001,"=1",P2:P100001,"=Filled",O2:O100001,"=Prime",N2:N10001,"=January")".

I am getting the "A value used in the formula is of the wrong data type" error from this formula, and my guess is because these parameters are formulas, rather than values, because when I add value to R, P, O, and N then this formula resolves fine.

Does anyone know a workaround I could apply so that I can use this SUMIFS formula without having to actually add value to my sales amount, month sold, etc. columns? I've Googled around and around on this one and I can't seem to find anyone that had my same issue.

Thanks in advance, Excel Heroes!

 

[mfexcel]

you don't need "=" in the criteria for Text.