Trying to do a work project that collates the number of depots that don't have manifests and trunk headers.

  • Column A: Depot Numbers
  • Column B: No Manifest record (i.e if Colum B 2 - 200 contains "value x" then means no manifest)
  • Column C: Same as column B but for Trunk Headers instead of Manifests

  • Column D: List of Depot numbers in numerical order (2 - 200)

  • Column E: Collated total of "no manifests" from column B "cells 2 - 200" if column A collates with the relevant Depot Number (i.e Column D4 will be Depot 3 and E4 will show the total number of Depot 3's that don't have manifests collated from Column A and B (column A is depot number & column B is no manifest))
  • Column F: Same as Column E but for No Trunk Header instead of No Manifest.

Can someone please assist as I'm not sure if I need REGEXMATCH or something else.

  • pls share a copy of your sheet and include an example of desired output
    – user0
    Apr 9 '19 at 23:36
  • There is a link to the spreadsheet, I decided to change it a little bit. Basically trying to get Colum D and E to display the total count for each depot from Column A and B. Needs to search for a "Text Value" then increase the count in Column D and E that corresponds to the given Depot number Apr 10 '19 at 0:15
  • which means if UKD002 if found in A2:A200 it needs to increase the corresponding value in Column D by 1 for UKD002 (if that makes sense?) Apr 10 '19 at 0:19
  • nope, not a close, pls provide some more details. it's just not clear what's the logic nor how should the final result look like. do you want to count unique occurrences from column A if found in column C ??
    – user0
    Apr 10 '19 at 0:32

 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1 
  label count(Col1)''"), 2, 0)))


demo spreadsheet

  • Thank you for your formula, although I have a question as I pasted this formula in Cell D2 and it returns a #REF! error saying data in the cell would be replaced if formula was placed in that cell. Error Array result was not expanded because it would overwrite data in D3. Apr 10 '19 at 1:06
  • yeah. delete stuff in D3 onwards to be able this formula to roll out
    – user0
    Apr 10 '19 at 1:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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