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