Getting a count once certain criteria are met [closed]

Question

I have a sheet that keeps track of files and their corresponding boxes. I am looking for help with a formula that will keep a count of how many boxes are completed. In the column "Box #" is the box number that the file is in. In the column "REACC." is a drop-down list with yes or no. When all files are marked "yes" for a particular box it will return a value of 1. When all files for another box are all marked "Yes", then I get a return of 2 and so on. I am guessing that the formula would somehow get a count of how many records are in each box and then get a count of records that are marked with a "Yes" for that box. If the two counts equal each other then return a value of 1. If the two counts don't equal each other then the value would be 0 or null. Here is an example of the sheet in question. Sample1 The example shows all files except one in Box 1 are marked "Yes" this box then would not be counted as complete. Also, all the files for Box 2 are marked "Yes" so, this box would be counted as complete and return a count of 1.

Answer 1

Try this formula in Cell G2:

=ARRAYFORMULA({"Full Boxes"; IF(len(A3:A),if(iferror(vlookup(B3:B,query({query({A2:F},"select Col2, count(Col2) where Col1 is not null group by Col2 label Col2 '', count(Col2) ''"),query({A2:F},"select Col2, count(Col2) where Col1 is not null and Col6 contains 'Yes' group by Col2 label Col2 '',count(Col2) ''")},"select Col1 where Col2 = Col4"),1,0),0)<>0,1,0),iferror(1/0))})

---

Comments

• Arrayformula enables the formula to adapt to the number of rows of data in the spreadsheet
• {"Full Boxes";
  • the curly bracket indicates the beginning of an array,
  • "Full Boxes is a text value that will be displayed in Cell G2
  • ";" the semi-colon means that the array contents are stacked.
• IF(len(A3:A) this limits the formula to display only in rows that have contents in Column A
• if(<<condition>>,1,0) this displays either a 1 or 0
• Condition: iferror(vlookup(B3:B,<<lookup range of full boxes>>,1,0),0)<>0
  • vlookup looks up the box number (Column B) in a list of full boxes
  • iferror(<<vlookup>>,0) - returns the value of te lookup of "0" (zero) if there is an error (i.e. no match)
• Lookup Range
  • query({query({A2:F},"select Col2, count(Col2) where Col1 is not null group by Col2 label Col2 '', count(Col2) ''"),query({A2:F},"select Col2, count(Col2) where Col1 is not null and Col6 contains 'Yes' group by Col2 label Col2 '',count(Col2) ''")},"select Col1 where Col2 = Col4")
  • this is a nested query with two elements.
    • the first select Col2, count(Col2) lists the box numbers and the number of folders in each box.
    • the second select Col2, count(Col2) where Col1 is not null and Col6 contains 'Yes' lists the box numbers and the number of folders with a value of "yes" in Column F
  • the final query : select Col1 where Col2 = Col4 creates a list of Box numbers where the number of folders in each box is equal to the number of folders marked "Yes"

---

SAMPLE

---

Update based on OP data

The OP data layout has several differences to the sample (above)

• Yes/No is in Column E
• Only "Yes" values are recorded. This requires an additional nested query to identify the folders that do NOT have 'Yes'.

The following formula is entered in Cell F1:

=ARRAYFORMULA({"Full Boxes"; if(IFERROR(vlookup(B2:B1993,query({query({A1:E},"select Col2, count(Col2) where Col1 is not null group by Col2 label Col2 '', count(Col2) ''"),query({query({A1:E},"select Col2, count(Col2) where Col1 is not null and Col5 contains 'Yes' group by Col2 label Col2 '',count(Col2) ''");Query(ArrayFormula(iferror({A2:A,{" "}/row(A2:A),B2:E},0)),"Select Col3,sum(Col2) where Col1 is not null and not Col6 contains 'Yes' group by Col3 label Col3 '',sum(Col2) ''",0)},"select Col1, sum(Col2) group by Col1 order by Col1 label sum(Col2) ''")},"select Col1 where Col2 = Col4"),1,0),0)<>0,1,0)})

---

Sample snapshot

---

List of Full Boxes

For a list of the full boxes, enter this formula in any column in row#1

=ARRAYFORMULA({"List: Full Boxes"; query({query({A1:E},"select Col2, count(Col2) where Col1 is not null group by Col2 label Col2 '', count(Col2) ''"),query({query({A1:E},"select Col2, count(Col2) where Col1 is not null and Col5 contains 'Yes' group by Col2 label Col2 '',count(Col2) ''");Query(ArrayFormula(iferror({A2:A,{" "}/row(A2:A),B2:E},0)),"Select Col3,sum(Col2) where Col1 is not null and not Col6 contains 'Yes' group by Col3 label Col3 '',sum(Col2) ''",0)},"select Col1, sum(Col2) group by Col1 order by Col1 label sum(Col2) ''")},"select Col1 where Col2 = Col4")})

---

Sample (extract)