Using a VLOOKUP result as criteria in a COUNTIF/S formula

[willjansen]

Hi there, long time reader, first time poster.

I've been searching all morning for an answer and haven't had much success.

I'd like to shorten the below formula by using the vlookup result as the criteria in the countif/s formulas. The idea (apart from shorten the formula) is to add additional criteria into the "vlookup result" rather than in the countif/s formula. Using a reference cell instead of the vlookup doesn't help either.

= IF(
AND(
$D$9 = "",
$D$10 = ""
),
COUNTIF(
Table1[Enquiry_Source__c],
{ "Agent Referral",
"Builder Referral",
"Builder Referral Program",
"Developer Referral",
"Staff Referral"
}
),
COUNTIFS(
Table1[Enquiry_Source__c],
{ "Agent Referral",
"Builder Referral",
"Builder Referral Program",
"Developer Referral",
"Staff Referral"
},
Table1[Start of Month],
">=" & Report!$D$9,
Table1[End of Month],
"<=" & Report!$D$10
)
)

I've tried the below but get a '0' as the result.

=IF(
AND(
$D$9 = "",
$D$10 = ""
),
COUNTIF(
Table1[Enquiry_Source__c],
VLOOKUP(
I13,
Processing!$G$1:$H$7,
2,
FALSE
)
),
COUNTIFS(
Table1[Enquiry_Source__c],
VLOOKUP(
I13,
Processing!$G$1:$H$7,
2,
FALSE
),
Table1[Start of Month],
">=" & Report!$D$9,
Table1[End of Month],
"<=" & Report!$D$10
)
)

Any suggestions will be appreciated.

Regards,
Will.

 

[Mike Szczesny]

Perhaps if you can provide a small data sample and the results you want. This could help others provide perhaps a more efficient formula for what you need.