Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-25-2023, 01:50 AM
lcggrp lcggrp is offline Count discounting products Windows 10 Count discounting products Office 2016
Novice
Count discounting products
 
Join Date: Oct 2023
Posts: 4
lcggrp is on a distinguished road
Default 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.
Attached Files
File Type: xlsx Forum.xlsx (15.0 KB, 2 views)
Reply With Quote
  #2  
Old 10-25-2023, 08:28 AM
p45cal's Avatar
p45cal p45cal is offline Count discounting products Windows 10 Count discounting products Office 2021
Expert
 
Join Date: Apr 2014
Posts: 855
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Quote:
Originally Posted by lcggrp View Post
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
Reply With Quote
  #3  
Old 10-25-2023, 07:32 PM
lcggrp lcggrp is offline Count discounting products Windows 10 Count discounting products Office 2016
Novice
Count discounting products
 
Join Date: Oct 2023
Posts: 4
lcggrp is on a distinguished road
Default

I'm okay with that. Code works. Thank you so much. Really appreciated it.
Reply With Quote
  #4  
Old 10-25-2023, 11:25 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Count discounting products Windows 10 Count discounting products Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,747
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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
Count discounting products 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
Count discounting products FREE resources to learn M$ products... zyzzyva57 Office 4 05-16-2009 07:31 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:48 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft