#1
|
|||
|
|||
Count discounting products
Hello All,
Good Day! Hoping to get the function or formula for this one. We have a promo for a certain products that if you buy both Candy1(FRES CANDY under the "SUBBRANDNAME" on Excel file) and Candy2(KOPIKO CANDY under the "SUBBRANDNAME" on Excel file) you will get a discount. So I would just like to count or have a result of 1/avail per invoice. I already removed the duplicates on the Sheet2. Thank you. |
#2
|
||||
|
||||
Quote:
Code:
=AND(COUNTIFS(Sheet1!$D$2:$D$100,D2,Sheet1!$H$2:$H$100,"FRES CANDY")>0,COUNTIFS(Sheet1!$D$2:$D$100,D2,Sheet1!$H$2:$H$100,"KOPIKO CANDY")>0) More flexible for varying source table length if you convert it to a proper table and use: Code:
=AND(COUNTIFS(Table1[Invoice '#],D2,Table1[SUBBRANDNAME],"FRES CANDY")>0,COUNTIFS(Table1[Invoice '#],D2,Table1[SUBBRANDNAME],"KOPIKO CANDY")>0) Code:
=COUNTIFS(Sheet1!$D$2:$D$100,D2,Sheet1!$H$2:$H$100,"FRES CANDY") & "/" & COUNTIFS(Sheet1!$D$2:$D$100,D2,Sheet1!$H$2:$H$100,"KOPIKO CANDY") Same if a table: Code:
=COUNTIFS(Table1[Invoice '#],D2,Table1[SUBBRANDNAME],"FRES CANDY") & "/" & COUNTIFS(Table1[Invoice '#],D2,Table1[SUBBRANDNAME],"KOPIKO CANDY") |
#3
|
|||
|
|||
I'm okay with that. Code works. Thank you so much. Really appreciated it.
|
#4
|
||||
|
||||
Please mark thread solved ( thread tools) Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
count formula to count based on multiple criteria and return unique rows | Sphinxakop | Excel | 0 | 02-01-2023 12:18 PM |
Why does the total row of my pivot table not tie to the real count using distinct count | wheddingsjr | Excel | 4 | 09-01-2020 09:52 PM |
How do MS Office products link to URLs? | stegraham | Office | 2 | 10-22-2013 04:52 AM |
All Office products stop working | lethlean | Office | 1 | 05-18-2013 07:07 AM |
FREE resources to learn M$ products... | zyzzyva57 | Office | 4 | 05-16-2009 07:31 AM |