Search code examples
if-statementgoogle-sheetssumgoogle-sheets-formulasumproduct

SUMPRODUCT, Multiple criteria, IF, Blanks treated as 1

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.

Here is the table

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
Solution

  • 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<>"")

    enter image description here

    update:

    =SUMPRODUCT(B2:B11, IF(C2:C11, C2:C11, 1), E2:E11<>"")

    enter image description here