COUNTIFS in VBA with data on multiple sheets

[uclabruwin]

Hello All, :hi:

First time in this forum and new to VBA, so I appreciate any help I can get.

Trying to create code for a COUNTIFS function to return values from another worksheet.
The two worksheets being used are:

Central Summary: where my value is to be returned and that has the "text" i want to search in the 2nd worksheet
Central Data: the data sheet i want referenced and counted.


I want the script to return value into Cell C16 of my "Central Summary" worksheet;

I need the code to search column M in my "Central Data" worksheet for text in Cell A23 from the "Central Summary" worksheet, then using that criteria, search the "Central Data" worksheet again for the words "On Hold" in Column A and return the sum of those occurrences.

The current working formula is below

=COUNTIFS('Central Data'!M:M,'Central Summary'!A23,'Central Data'!A:A,"On Hold")

Thank you in advance for all the help.
A.

[SamT]

Maybe something like:

=CountIfs('Central Data'!A:A,COUNTIFS('Central Data'!M:M,A23),"On Hold")

[p45cal]

=CountIfs('Central Data'!A:A,COUNTIFS('Central Data'!M:M,A23),"On Hold")
Put that in square brackets and you should have your vba code:
Sheets("Central Summary").range("C16").value=[CountIfs('Central Data'!A:A,COUNTIFS('Central Data'!M:M,A23),"On Hold")]

[uclabruwin]

Thank you SamT and p45cal for responding to my thread. Most appreciated.

I've tried the code and get a #VALUE! error returned.
I've tried modifying the "On Hold" value to reference a range cell instead, and still have the same issue.

Again, appreciate any support i can get from the group.

[SamT]

3 steps


Write the first formula in a spare cell referencing A23. Test it
Write the second Formula in another cell referencing the first cell. Test it
When it all works, Substitute the first formula into the second formula where it references the first one.