Count discounting products

[lcggrp]

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.

[p45cal]

Originally Posted by lcggrp

So I would just like to count or have a result of 1/avail per invoice.

Not at all clear on what you want to count. As a starter, I can get a TRUE/FALSE on whether the invoice contains at least one row of FRES CANDY and at least one row of KOPIKO CANDY, with in cell E2:

=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)

copied down.
More flexible for varying source table length if you convert it to a proper table and use:

=AND(COUNTIFS(Table1[Invoice '#],D2,Table1[SUBBRANDNAME],"FRES CANDY")>0,COUNTIFS(Table1[Invoice '#],D2,Table1[SUBBRANDNAME],"KOPIKO CANDY")>0)

You can have a count of each with the formula:

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

which will show how many rows of each.
Same if a table:

=COUNTIFS(Table1[Invoice '#],D2,Table1[SUBBRANDNAME],"FRES CANDY") & "/" & COUNTIFS(Table1[Invoice '#],D2,Table1[SUBBRANDNAME],"KOPIKO CANDY")

2023-10-25_163644.jpg

[lcggrp]

I'm okay with that. Code works. Thank you so much. Really appreciated it.

[Pecoflyer]

Please mark thread solved ( thread tools) Thx