Count discounting products

lcggrp · #1 10-25-2023, 01:50 AM

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 · #2 10-25-2023, 08:28 AM

Quote:

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:

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)

copied down.
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)

You can have a count of each with the formula:

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

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

Code:

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

2023-10-25_163644.jpg

lcggrp · #3 10-25-2023, 07:32 PM

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

Pecoflyer · #4 10-25-2023, 11:25 PM

Please mark thread solved ( thread tools) Thx

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