SUMIFS Formula treat differently 018 and 18

[Lars Ole Sørensen]

Hi,

I have a problem with the SUMIFS formula. I am wondering if it for example is possible to treat 018 and 18 differently in the SUMIFS formula. These numbers will vary so what is important is that the formula treats numbers starting with 0 differently than the numbers after 0. If this is not possible with the SUMIFS formula, then i am open for others methods to get the sum correct (not pivot table).

Kindly Regards
Lars Ole

 

[Mike86]

The main issue is probably that the "018" is text and not a number. If this will happen in your data set, you could use the VALUE function to convert any text to a number. If you sum over a range of values and want to convert any text in that range, you will need to enter the SUM as an array function (Cntrl-Shift-Enter)

something like this should work:

=sum(value(a6:a35))

 

[Hui]

D6: =SUMPRODUCT(tbl_dataMaritech[landetkvantum],(tbl_dataMaritech[fartoy]=$B$1)*(tbl_dataMaritech[maer_tur]=A6)*(tbl_dataMaritech[Fersk/frys]=$A$3)*(tbl_dataMaritech[Fiskeslag avviksrapport]=$D$4))

F6: =SUMPRODUCT(tbl_dataMaritech[Total salgsverdi],(tbl_dataMaritech[fartoy]=$B$1)*(tbl_dataMaritech[maer_tur]=A6)*(tbl_dataMaritech[Fersk/frys]=$A$3)*(tbl_dataMaritech[Fiskeslag avviksrapport]=$D$4))

Copy both down

 

[Hui]

All the values in the Table are Text

 

[Lars Ole Sørensen]

Thanks for the tips Mike86 and Hui. Hui, the formulas you posted worked perfectly for my purpose.

The Table:
I have the format "General" for the values in the table.

Regards
Lars Ole