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

#### hparx

##### New Member
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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Joe4

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]``

Replies
3
Views
441
Replies
5
Views
832
Replies
1
Views
209
Replies
1
Views
88
Replies
4
Views
468

1,141,074
Messages
5,704,150
Members
421,330
Latest member
eiksnamra

### 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.

### Which adblocker are you using?

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

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