Sumif / Sumproduct enhancement or alternative where item could be text or value

[benoj2005]

Hi

Anyone got any ideas of alternatives to a SUMIFS or a way of enhancing to cope with criterias that are stored as value or text so need conversion to ensure the criteria and lookup range as both in a consistent format.

When using a SUMIFS (Column H) I have been adding a helper column to convert the criteria range to text in the source data. I was wondering could this helper column be avoided (Column D) in the attached simple example.

I've tried using a SUMPRODUCT (column I) where the text conversion is done to the lookup array as part of the formula but the performance of the sumproduct even though real data set is only quite small is really quite slow where a recalc happens everytime data is added to the data table so I'm searching if there's a different approach that's more efficient.

Whilst this is a simple mock up please keep the solutions capable to dealing with the personnel numbers being in any order and quantity in the source data.

Thankyou

[Sam Capricci]

this appears to work, is this what you are looking for?... =SUMIFS($C$3:$C$10,$A$3:$A$10,F3*1)

[benoj2005]

Ah nice so basically it doesn't matter what format the Personnel numbers are in (text or values) in column A. I guess my sumifs text conversion also would of worked. Could of sworn values being in text or values has caused problems in the past so thought I always had to make sure both the lookup value and the lookup range had to be consistent in values or text formats

[Sam Capricci]

if you want it in a sumproduct this works too... =SUMPRODUCT((--$A$3:$A$10=F3*1)*($C$3:$C$10))
but you need to convert both sets (A3:A10 and F3) to numeric for it to return the same numbers.