Summing two sets of cells by a common multiplier

[wyturieowk]

Hi, so I'm massively stumped with a radio ad scheduling/totaling spreadsheet that I'm refining. I've covered acres of websites looking for a solution as I'm loathe to try to explain it (as I'm a novice), but a couple weeks of whittling on this problem has brought me to my knees:

Context:
Line# Billing Rate Ads/Wk Total Mnths Ad Rate Total Ads Line Cost
Line 1 $0 3 1 $14.00 3 $42 (this line expresses Ad Rate x Total Ads: 3x14=42)
Line 2 $100 0 1 $0 0 $100 (this line expresses Billing Rate x Billing/Mnth: 1x100=100)

I'm trying to reflect either the Total Ads X Ad Rate (if any) OR Billing Rate X Billing/Mnth (if any).
The below formula uses a common multiplier (V31) to sum the Ad Rate*Total Ads, but it isn't summing up the Billing Rate*Total Mnths.
Both options allowable on the same order line (so either Line 1 or Line 2 can be expressed), but I can't seem to get Line 2 scenario to work in this formula:

=IF(V31=" "," ",IF(X31=" "," ",IF(T31=" "," ", SUM(X31*V31,(T31*V31))))). I basically need EITHER...SUM X*V (line 1 scenario) OR T*V (line 2 scenario), but not both or any permutations thereof. V is the common multiplier, and I need it to sum X*T or X*V. It currently sums ad rates alone or both ad and billing rates when both are expressed, but not the billing rate only. What am I missing? I feel it's right in front of my nose....

Thanks much for any help.

[Special-K]

I don't understand this fully.

"I'm trying to reflect either the Total Ads X Ad Rate (if any) OR Billing Rate X Billing/Mnth (if any)."

How do you decide which you want?

Can't you have two columns for cost? One for X*V and one for T*V ?

[wyturieowk]

Hi Special-K, thanks for taking a look at this and letting me know I made little sense :-).
The reason I don't just separate the total column to two separate billing types is because the sheet is used by other people with other systems(...aaaaand there would be plagues and a certain smiting upon me if I did...).

However, I figured it out, thanks to your question which put the idea in my head.
Instead of your suggestion of two separate columns, I decided to use an additional multiplier. Instead of 1 common multiplier for two billing scenarios, I now have a dedicated multiplier for each billing scenario:

=IF(X43="","",IF(T43="","",SUM(X43*V43+T43*R43)))

V43 and R43 are the multipliers, which pull the multipliers from other totals or date range expression).

Now, with a little VBA or conditional formatting I think I can prevent both billing types being used on a single line depending on what rate type is used (either flat rate billing in R43, or a per Ad rate in V43)

I was over-complicating it. Your suggestion was the idea I needed. Thanks!!!