SUMIFS formula with multiple criteria not working?

The formula is correct but source data include text and numeric. There is two way. Correct source or use SUMPRODUCT

Please Login or Register  to view this content.

In column E (and in column D) most of the "numbers" are being treated as text values, as there is a comma instead of a decimal point. I know that is the standard in Germany, but numbers usually get converted into the appropriate format when files are passed between countries.

I highlighted those two columns and did Ctrl-H (Find & Replace) and changed the comma to a full-stop, and then the formula seems to work - there is only one category which meets the criteria (Food & Drinks) resulting in -12.8.

Hope this helps.

Pete

Hello BMV! Thanks a lot for your help however i tried your formula but an "#VALUE!" mistake appears do you have any idea why this could be?

Hello Pete! Thanks a lot for your help, i changed the commas for dots and it worked out as you said. However, I did the calculations manually and there should be more categories with results, not only "Food & Drinks" (

Hey BMV! Thanks a lot for your help I think the sumproduct formual is missing something, since a "#value!" mistake is appearing, but I believe the idea is the correct one so I am just going to double check it.

Many thanks!