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

[hparx]

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.

 

[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:

[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:

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

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