SUMIF help please

SALBOL1

New Member
Joined
Oct 12, 2004
Messages
36
Hello --

Another follow-up from yesterday --- I have columns that look like the following:

A B C D E F
1 Month 1 2 3 4 5
2 Promo 43 52 37 OFF OFF
3 Sales 21 20 23 25 27
4 Margin 297 632 911

I've got Margin as a cumulative number which considers sales from previous months (less a churn amount of those customers) -- and a Promotion period which will be variable in the model. In this instance, it happens to be a 3 month period. I'm trying to create a SUMIF formula to account for these varied timeframes, so that I will sum the Promo sales, apply a churn factor, and then * by a margin --- and also do the same for my regular Sales customers as well. Here's what I've tried with a SUMIF formula, but I'm getting funky results:

If In C4:

SUMIF($B$2:B2,"B2>0")*(1-$churn rate)*($margin)+SUMIF($B$3:B3,"B3>0)*(1-$churn rate)*($margin)

If in D4:

SUMIF($B$2:C2,"C2>0")*(1-$churn rate)*($margin)+SUMIF($B$3:C3,"C3>0)*(1-$churn rate)*($margin)

etc....

I know I'm being thickheaded here --- but need some help. I should probably have something that indicates if row 2 doesn't = "OFF" (promo over), but in months 4 and 5 I still have to account for the base on those customers that will churn away from those that I sold in those 3 promo months...
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Found my own answer, instead of having SUMIF($B$1:G1,"G1>0"), I just replaced it with SUMIF($B$1:G1,">0") and then mult by the other factors and now the formual works...dk why specifically, but it does.....
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top