How to count/divide two cell information within a sumif forumula?

Hi everyone,

I have been fighting with this for two days now: I have a table with various projects, funded by various donors, happening in different continents and countries. What I would like to do is to establish some statistics for each donor. I want to show how much money donor A spent for projects in Latin America, Sub-Saharan Africa or East Asia. The problem is that some projects occur in multiple countries, therefore only an equal share should be assigned to a contribution with a continent.

I have been using a Sumifs-Formula: SUMIFS(grant by donor;region "x"; no of projects "1") ( I was working with lists).

But this is what I need: If a project region happens in two regions, I need to divide the grant of donor A by the numbers of regions the project occurs and add it up to the respective total number and finally sum-up all numbers. The number occurs in a different column (even though it would be nicer to get ride of this column and embed it into the formula). I need one final number in the end without having to add a different column.

I tried various forms of if-formula or sum-ifs, but it didn't work. (Something like =SUMIFS(grandpa;LATIN_AMERICA;"x";No_of_regions;"1")+=SUMIFS(grandpa/2;LATIN_AMERICA;"x";No_of_regions;"2"+...)....


I attached a smaller, fake dummie of the original table in order to illustrate what I need. Thanks in advance for you help!

I May have read this the wrong by but place this formula into cell B15 and let me know if this is what you are after

Please Login or Register  to view this content.

Hi,
Thanks a lot. This is already an improvement - It's quite close. If I understand this formula correctly it first counts all the amount available by grandpa in Latin_America divided then by the total numbers of region. The original sheet has different amounts paid by "grandpa" ( which I admit did not become quite clear in this dummy). This calculation would need to happen for every single row first and then the intermediate results need to be added together.

If i add an x to 'J3' in this dummy (claiming it happens in LA as well), I have two projects paid by "grandpa" which are in Latin America. The investment of grandpa in Latin America would be: (5.000/3)+(10.000/3)=5000 EUR.

The formula you proposed provides 2.500€ as a result. So it's a good first step.

Hi, anyone who could help?

Hi Eldrowski, perhaps this formula in B15 and copy across?

Formula:

Please Login or Register  to view this content.

ps: In post#3 I think you mean J7 instead of J3?
ps: N3 and A19 named differently



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

I just tried that formula in B15 (using the one that you attached in post #1) and get 1666.6667.
Then I copied to rest of the cells without issue except for B19:D19 which gives me an #N/A due to N3 and A19 named differently

If you still get the #N/A, try upload the file with the error so that I can take a look



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Hi, the #N/A error war actually my mistake, I entered it into a wrong version of the excel file. Sorry, for that ( I blame Monday) - but after I put it into the right document, I got a #REF error.

Interesting, did you try my formula with the attachment that you posted in this thread? or you tried it with your actual file?



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Copy of dummie-1.xlsx

This is the file with the #REF error.

Thanks a lot for your help, alvin!

No I tried it with the attachment. The actual file is way bigger and more complex. Maybe it's a version issue? I don't know

You're welcome.

Oh dear, you took out two commas from my formula
Try copy again my formula in post #5 and paste to B15 shall work.



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

Well, I did it because this is what I get when I paste the formula: what I get.jpg

I have a German version of excel where you use ';' instead of ',' within a formula. Should I put two semicolons then?

Oh I see...
Yea, please change all commas to semicolons as we have different regional settings



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

After adding the second semicolon it worked and I get 1666,666667


THANKS!

It's perfect!

Thanks from a very happy me

You're welcome, glad you finally got it



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin