Sum if problem

[Mariya]

Hello,

I have the following questions. On the file below on the frist sheet is income and on the second with sum if function the revenues from 4 of the companies. The problem is the first 2 companies have the sama name. The second one consist the full name of the first company. How can I make my sum if function to distinguish it. At the moment it gives the sum of first company as sum of first and second one.

Mariya

 

[AliGW]

Each company needs a unique ID number. Add a column with a number that identifies each company and then use that for the SUMIF instead of the name.

 

[vletm]

Mariya
1) Your SUMIF-formula has one typo,
=SUMIF('Bank '!$F$2:$F$68;Revenue!B3;'Bank '!$C$2:$C$68)
>> results are correct!
2) cells B3 & B4 are different ... SUMIF works as it should be!

Which sums should be incorrect?

 

[Mariya]

vletm, the sum in "ЗАД Алианц България" at the moment includes the sum of "ЗАД Алианц България Живот" as well. It should give the result of itself alone. That's why I guessed it's because of coincidence on the names.
Ali, I will try your suggestion.

 

[vletm]

Mariya
How many "ЗАД Алианц България" are there?
How many "ЗАД Алианц България Живот" are there?
Did You even fixed that formula?

 

[Mariya]

AliGW, I did it and it worked, thank you!

 

[Mariya]

vletm, I fixed the formula, thank you! It didn't catch it because it was empty space. I made it and gave another name to "ЗАД Алианц България" and the results are already real. Actually, you showed me the mistake, which I did not notice

 

[vletm]

Mariya
Sums of
"ЗАД Алианц България" and "ЗАД Алианц България Живот" weren't mixed.
if those meant different companies!
You didn't answer: Which sums should be incorrect?
How did You checked those?

 

[Mariya]

Vletm, sorry for the late reply. These are 2 different companies and the formula thinks them for one and first counts them together and then the second one separately and thus the sum was aggregated instead real. When I change one of the names, everything came in place. I hope this reply your questions.

 

[vletm]

Mariya
... hmm?
You didn't answer any of my questions...
#3 reply 'fixes' Your challenge
If names are different or ID numbers are different then 'no mix'..
Excel won't think...