-1

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.

2
  • Hi, welcome to WebApps. Thanks for providing a sample spreadsheet. It requires permission to access it and that can be a disincentive for volunteers to persist with the question. I strongly suggest that you share it so that anyone with the link can access it.
    – Tedinoz
    May 18 at 9:38
  • @Tedinoz I will do that when I get home. It is probably due to my work's permissions since I created this while using my work's google drive. Thanks for the heads up!
    – NateM
    May 18 at 19:00

1 Answer 1

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

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

OP data


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)

snapshot

9
  • I copied and pasted your suggested formula into G2 but I am getting a return value of 0. I do have some of the records that are marked "Yes" for a whole box but still show "0." Not a "1" like your sample shows for all records marked "Yes" for a particular box. I think it's on the right track.
    – NateM
    May 16 at 16:00
  • Maybe a better way is to have a query count how many records are in a particular box and reference how many of those same records are marked "Yes." If those numbers equal each other, then that box is complete. The number of boxes would then increment by one every time all records are marked 'Yes' for a box. It looks like that's the way you are going, but the final answer/return would just be how many boxes are completed. I hope that makes sense and is not too convoluted.
    – NateM
    May 16 at 16:01
  • @NateM There was a "old" formula that made it into my answer (it was located at the very bottom of the Comments) and I have just deleted it. The formula to use is the one at the top =ARRAYFORMULA({"Full Boxes"; ..... You said "I am getting a return value of 0. I do have some of the records that are marked "Yes" for a whole box but still show "0." Not a "1" like your sample. If you are getting this result on your own data, then it's impossible to comment without having access to your data. Please share your spreadsheet and explain EXACTLY which records are returning unexpected results.
    – Tedinoz
    May 18 at 9:54
  • @NateM Maybe a better way is I was impression that this is exactly what the formula does. Read the "Comments" section under "this is a nested query with two elements".
    – Tedinoz
    May 18 at 9:58
  • I think I fixed the link to the sheet. I had to sanitize the sheet by removing Col. F. Give it a try and let me know if it works. Sorry about that. I understand what you are saying about the nested queries. The problem still stands that when I copy and paste your formula into G2 it shows all zeros no matter if the files in a particular box are all marked "Yes". The other question I have is in your example the formula is inserting a "1" next to each file that has "Yes". Is there a way to have it show an individual count of each box completed? I show this in my example sheet.
    – NateM
    May 19 at 0:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.