| A | B | C | D | E | |
| 1 | LOCATION | AMOUNT | Mr. A | Mr. B | |
| 2 | LAS | Mr. A | $0 | ||
| 3 | LAS | Mr. B | $10,000 | ||
| 4 | LAS | Total | $10,000 | ||
| 5 | LAS | TV | $2,000 | $2,000 (manual input) | |
| 6 | LAS | Radio | $8,000 | $8,000 (manual input) | |
| 7 | SFB | Mr. A | $2,500 | =C7/C9 | =C8/C9 |
| 8 | SFB | Mr. B | $2,350 | ||
| 9 | SFB | Total | $4,850 | ||
| 10 | SFB | TV | $0 | ||
| 11 | SFB | Radio | $4,850 | =D7*C11 | =E7*C11 |
<tbody>
</tbody>
So I have the above data. Now, I have been just manually inputting the dollar amount according to either Company or Sponsor.
For example, since all the advertisement for LAS was sponsored by Mr. B, I would put $2,000 in E5 and $8,000 in E6.
For SPB, both the Mr. A and sponsored for the advertisement, I would calculate how much of the total Mr. A contributed and how much Mr. B contributed.
I would do "=C7/C9" in D7 and "=C8/C9" in E7. Then I would use those percentage and multiply by the amount spent from each advertising type (see the table).
Now what I want to do is maybe use "IF" formula to calculate the numbers. So that all I would have to do is input the amount in Column C for each month, and formulas in Column D and E would pick up the values and automatically do it. Now, note that locations vary on the amounts, the person who sponsors, and the media type that was advertised.
Greatly appreciate any help!
