I've done quite a bit of googling, but am having difficulty combining different formulas together. I've tried to be as detailed as possible in describing what I want to achieve.
Context:
- There's a table of items with prices (column B) and quantities (Qty) (column C).
- Any input in the exclusions column (column E) signifies whether that particular row is excluded from any/all promotions.
- In the Discounts table, Minimum (column H) refers to the minimum order total that must be met or exceeded in order to be eligible for the discount (column I).
- I would like to delete Column D (not just hide it), which is included for illustrative purposes only.
- If it makes a difference, I'm doing this on Google Sheets.
Looking for one formula in cell H5 that incorporates the following criteria:
- Any input in column H indicates that row's discount is active. If column H is blank, then that row's discount is inactive.
- If no discounts are active (i.e., column H is empty), then do nothing and leave the cell H5 blank.
- Only one discount can be active at a time. If both are active, then H5 will remain blank. (H2 and H3 both have numbers in them right now for the sake of the example.)
- If Discount 1 is active and the minimum order total in column H is met, then H5 should show me a number that is the sum of rows with exceptions (i.e., sumproduct of columns B and C in rows with something in column E, treating blanks in Qty as 1), divided by the order total (sumproduct of columns B and C, still treating blanks in Qty as 1), multiplied by the discount in I2 ($10). This pro-rates the discount across the whole order, reducing the discount by the proportion of excluded rows. i.e.,
(B3*1 B6*C6 B8*C8)/D12*I2 = $8/$27*$10 = $2.96 - If Discount 2 is active and the minimum order total is met, then H5 should show me a number that is the sum of rows with exceptions, divided by the order total, multiplied by the product of the discount percentage in I3 and the order total. i.e.,
(B3*1 B6*C6 B8*C8)/D12*(I3*D12) = $8/$27*(0.1*$27)
What I have so far:
- To get the total cost of this list of items, while treating any blanks in the Qty column as a 1 by default, I found the formula below in my search. (Truthfully, I don't understand how the above formula works, particularly because sumproduct(isblank(C2:C11) C2:C11) gave me the same result despite not incorporating column B.):
=sumproduct((isblank(C2:C11) (C2:C11)),B2:B11)`
- To account for the different discounts, I've come up with the below:
=IF(COUNTIF(E2:E11,"<>"&"")>0=TRUE,IF(AND(H2="",H3=""),"",IF(AND(H2<>"",H3<>""),"",IF(AND(H2<>"",H3="",D12>=H2),sumif(E2:E11,"<>"&"",D2:D11)/D12*I2,IF(AND(H2="",H3<>"",D12>=H3),sumif(E2:E11,"<>"&"",D2:D11)/D12*I3*D12)))))
- I'm thinking I can replace all instances of D12 with my first sumproduct formula above.
- Missing: However, I'm getting stuck on how to replace "sumif(E2:E11,"<>"&"",D2:D11)" with something like a sumproduct formula of B and C with the added criteria of only summing rows where something is in column E. My confusion is in part because I don't understand how that first sumproduct formula works to be able to incorporate any "IF" statements.
I'm also open to other suggestions if there's a better way for me to go about this. Thank you in advance for your time!
Edit:
- Clarifying desired outcome: Currently, this section of my formula sumif(E2:E11,"<>"&"",D2:D11)=8. I would like to replace all references to column D in this, and rely on a sumproduct of columns B and C only. It should still equal 8.
- The results in cell H5 depend on what happens in the Discounts table.
- If H2=$20 and H3 is blank, H5=$2.96, which is H5=sumif(E2:E11,"<>"&"",D2:D11)/D12*I2
- If H3 = $20 and H2 is blank, H5=$0.80, which is H5=sumif(E2:E11,"<>"&"",D2:D11)/D12I3D12
- If H2 and H3 are both blank, H5=""
- If H2 and H3 are both not blank, H5=""
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Price | Qty | Total Cost | Exclusions | Type | Minimum | Discount | |
| 2 | Item 1 | $1 | 10 | $10 | Discount 1 | $20 | $10 | ||
| 3 | Item 2 | $1 | $1 | a | Discount 2 | $20 | 10% | ||
| 4 | Item 3 | $1 | $1 | ||||||
| 5 | Item 4 | $1 | $1 | Result: | FORMULA HERE | ||||
| 6 | Item 5 | $1 | 5 | $5 | abc | ||||
| 7 | Item 6 | $1 | $1 | ||||||
| 8 | Item 7 | $1 | 5 | $5 | |||||
| 9 | Item 8 | $1 | $1 | ddd | |||||
| 10 | Item 9 | $1 | $1 | efg | |||||
| 11 | Item 10 | $1 | $1 | ||||||
| 12 | $27 |
CodePudding user response：
formula of B and C with the added criteria of only summing rows where something is in column E
try:
=SUMPRODUCT(B2:B11, C2:C11, E2:E11<>"")
update:
=SUMPRODUCT(B2:B11, IF(C2:C11, C2:C11, 1), E2:E11<>"")
