Populate Formula based on Reference Value + Sumifs w/ Offset (?)

hparx

New Member
Joined
Nov 4, 2019
Messages
1
Hello -

I'm hoping for some help on two formulas/use cases I can't seem to figure out. Unfortunately, I couldn't quite figure out how to attach the date set but have included here with reference rows/columns for what I hope is an easier explanation:

Q1: I would like to populate cells based on a reference value. I've created the following formula but it's not working quite as anticipated:
'=IF(OR(B4="wm","pwm"),"",IF(B4="pcomm",D3*0.1225,IF(B4="pttl bill",D3+D4)))​

Essentially I would like the following to happen:
  • If Col B value = TTL BILL or PWM then "". This will trigger the team to add the hard coded value once the product is booked
  • If Col B value = PCOMM then (PWM X .1225). To note the PCOMM row always follows the PWM row.
  • If Col B value = PTTL BILL then (PWM + PCOMM). To note, the PTTL Bill row always follows PCOMM

Q2: We often add rows to the table as the year progresses. Not all products include commission. I would like to create a total row which takes into account not only the total to be billed (TTL BILL + PTTL BILL) but also accounts for rows that are added throughout the year. Rows can be added anywhere within the table but are often added directly above the total line.

I have used the following formula =SUBTOTAL(9,D4:OFFSET(D8,-1,0)) with success for divisions whose products do not have commission. However, I can't figure out a way to account for both the need to sum only the billable rows (TTL BILL + PTTL BILL) while also accounting for the addition of rows throughout the year. I tried a couple of iterations of sumif(s) with offset but it didn't seem to work

SAMPLE DATE SET w/ ANTICIPATED OUTCOMES. Please note for the first two rows below, for Q1, I would anticipate the value to return as "" prior to adding the hard coded "cost"


<tbody></tbody>
COL B
COL D
LINE ITEM
Cost Structure
Total Cost (net)
Jan-19
Feb-19
Mar-19
Apr-19
May-19
Jun-19
Jul-19
Aug-19
Sep-19
Oct-19
Nov-19
Dec-19
R4
L1
TTL BILL
$900
$20
$30
$40
$50
$60
$70
$80
$90
$100
$110
$120
$130
R5
L1.A
PWM
$110
$30
$40
$50
$60
$70
$80
$90
$100
$110
$120
$130
$140
R6
L1.A
PCOMM
$1,010
$4
$5
$6
$7
$9
$10
$11
$12
$13
$15
$16
$17
R7
L1.A
PTTL BILL
$1,145
$34
$45
$56
$67
$79
$90
$101
$112
$123
$135
$146
$157
Total Cost

$2,045
$54
$75
$96
$117
$139
$160
$181
$202
$223
$245
$266
$287

<tbody>
</tbody>

Thank you in advance for any help/insights you can provide.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Welcome to the Board!

Sometimes it is better to not ask too many questions in a single post. Often times, people may be able/willing to help with one question, but not the others, so will not reply back.

Let's look at your first formula:
Code:
[COLOR=#333333]=IF(OR(B4="wm","pwm"),"",IF(B4="pcomm",D3*0.1225,IF(B4="pttl bill",D3+D4)))[/COLOR]
You have an issue with your OR clause. Each part of the OR clause must be a COMPLETE statement, i.e. it should look like this:
Code:
[COLOR=#333333]=IF(OR(B4="wm",[/COLOR][COLOR=#ff0000]B4="pwm"[/COLOR][COLOR=#333333]),"",IF(B4="pcomm",D3*0.1225,IF(B4="pttl bill",D3+D4)))[/COLOR]
The second thing is, you did not indicate what you want to happen if none of thesre conditions are met. As is written, it will simply return the word FALSE in that situation.
To tell it to return something else, you will need to enter something for the last FALSE clause, i.e.
Code:
[COLOR=#333333]=IF(OR(B4="wm",[/COLOR][COLOR=#FF0000]B4="pwm"[/COLOR][COLOR=#333333]),"",IF(B4="pcomm",D3*0.1225,IF(B4="pttl bill",D3+D4,[/COLOR][COLOR=#ff0000][I]"what to return if no conditions met"[/I][/COLOR][COLOR=#333333])))[/COLOR]
 
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,762
Members
448,295
Latest member
Uzair Tahir Khan

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