Help with collect formula with multiple AND arguments

Verizon_Ryan

Hi folks,

I am currently using this formula, which works fine:

=IFERROR((AVG(COLLECT({#Working Days to Kickoff with KP}, {Verizon Received Date}, AND(@cell <= DATE(2023, 1, 31), @cell >= DATE(2023, 1, 1))))), "")

This gives me the average time we take to kick off our projects from the date we receive our projects from the customer. I am trying to add a second argument filter projects for various TPV (third-party vendors), but I can not seem to get the second argument to work. Here is what I've tried:

=IFERROR(AVG(COLLECT({Working Days to Issue PO}, {Verizon Received Date}, AND(@cell <= DATE(2023, 1, 31), @cell >= DATE(2023, 1, 1)), {TPV Name}, AND(CONTAINS("Opstel", {TPV Name})))))

=IFERROR(AVG(COLLECT({Working Days to Issue PO}, {Verizon Received Date}, AND(@cell <= DATE(2023, 1, 31), @cell >= DATE(2023, 1, 1)), {TPV Name}, CONTAINS(@cell, "Opstel"))))

With each of these, I get "INVALID Arugemt "INCORRECT ARGUMENT SET."

Am making this too difficult. Is there an easier way?

Eric Law

@Verizon_Ryan It seems like you are on the right path, but your second criteria is where you are messing up on. You just need "Opstel".

=IFERROR(AVG(COLLECT({Working Days to Issue PO}, {Verizon Received Date}, AND(@cell <= DATE(2023, 1, 31), @cell >= DATE(2023, 1, 1)), {TPV Name}, "Opstel")))))

Verizon_Ryan

I think that's closer and makes more sense, but still getting "#INCORRECT ARGUMENT SET"

Paul Newcome

Try this:

=IFERROR(AVG(COLLECT({Working Days to Issue PO}, {Verizon Received Date}, AND(@cell <= DATE(2023, 1, 31), @cell >= DATE(2023, 1, 1)), {TPV Name}, CONTAINS(@cell, "Opstel"))), "")

Your second one was closest. You just forgot to put your "value if error" piece back in at the end there.

Verizon_Ryan

Thank you @Paul Newcome that solved the logic error, but now it comes up blank so when I removed IFERROR and just changed it to to =AVG(COLLECT({Working Days to Issue PO}, {Verizon Received Date}, AND(@cell <= DATE(2023, 1, 31), @cell >= DATE(2023, 1, 1)), {TPV Name}, CONTAINS(@cell, "Opstel")))

so that I could see what the problem is I get "#INVALID REF" which can not be true because I triple checked all the sheet references and I'm using them in other formulas and they all work. When I removed the last part of the argmemt nad just used =(AVG(COLLECT({#Working Days to Issue PO}, {Verizon Received Date}, AND(@cell <= DATE(2023, 1, 31), @cell >= DATE(2023, 1, 1))))) it works fine, I just can't seem to get the argument to use "{TPV Name}, CONTAINS(@cell, "Opstel")" no matter what I do.

Paul Newcome

When entering the formula, instead of typing out the cross sheet reference, go through the steps as if you are creating it from scratch again. It could be that there is a hidden space somewhere or something like that.