Formula for Pivots - INDEX/MATCH, Nested IF

In F1 try:

Please Login or Register  to view this content.

But if you just need a Pivot Table, couldn't you put "Order ID" in the Row Labels, then "Sum of Product-Level Discount" and "Sum of Order-Level Discount" in the Values; and then let the numbers in that speak for themselves without grinding through some text lookup?

Thanks, Ben_Hensel. Your formula works, but could you please help me understand what it's saying? I've tried reading about the CHOOSE function but it is not making sense to me. I understand how SUMIF works, so I get that aspect of the formula. And I understand "CHOOSE" selects from a range of values based on an index. But the reference to "1" and "2", adding and multiplying SUMIF, and the order in which you list the output values - those things I don't understand.

Unfortunately, summing the PL discounts and OL discounts won't work for my pivot because I can have an unlimited number of rows per order. If I set the values to 1 (OL only), 2 (PL only), 3 (Both), or 4 (Check), and a pivot order returns "12", I could be looking at a 12-line order with OL-only discounts; a 6-line order with PL-only discounts; a 5-line order with two "Both", 1 "OL", and 1 "PL"; etc.

I realized after writing this I would need to return numerical values since the values section of my pivot would not return text (I could easily substitute the outputs for numbers in the formula, run the pivot, copy as a table, and INDEX/MATCH to text string values). I would appreciate if you kept explaining the formula using the text string values, though, as I won't confuse those with numerical reference locations.

CHOOSE is pretty much a Case structure. Something like this:
CHOOSE(math, return 1, return 2, return 3, etc)

So you do some math in the first term of the choose, and it must produce an integer between 1 and 255. And then the CHOOSE returns the term of the function that matches the integer from the first term.

It's basically a CASE structure.

So what I was doing in the first term of the CHOOSE was Boolean arithmetic.

Fundamentally what's going on is,
TRUE assess as 1 if you use it in math, and FALSE assesses as zero

So 1+ (sum1 >0) + 2*(sum2 >0)
If those (sum >0) assesses as TRUE, it gets a one, if it's FALSE, a zero.

So it works out as 1+ IF(sum1 >0, 1, 0) + (sum2 >0, 2, 0)
Returns 1 if neither is true (because 1+0+0), returns 2 if it's "OL only" (1+1+0), 3 if it's "PL only" (1+0+2), and 4 if it's both (1+1+2)

Then those numbers get fed into CHOOSE.