Create a countifs formula after a vlookup output

[Mattymoo]

Hi,
I have created a vlookup formula that will return a value if true and a text value if false.
I then need to do a count of the number of text results however the countifs formula still reads the vlookup output as a formula.
I have tried to break down the output and using sumproduct but i keep hitting a brick wall.
Can anybody shed any light on what im missing.

 

[Skybluekid]

What formula do you have?

It should look like =COUNTIFS(Range,VLOOKUP(Lookup,Range,column index,Matchtype),............)

 

[Mattymoo]

I am doing =countifs(criteria_range1,Criteria1,Criteria_range2,Criteria2).
Both ranges are looking up a vlookup formula. I have copied and pasted the output of the formula and can get it to work but going forward that would be a bit impractical.

thank you for replying.

 

[Skybluekid]

Sorry, a bit confused. Just re read your initial post. VLOOKUP returns a value from a range when given a value to lookup in the first column of that range. It cant return a range of values. You have said that it return a value if true and a text value if false? This sounds like an IF Statement. Are you able to post a snippet of data?

I have enclosed below a simple Countif with a VLOOKUP for the Criteria

Book1
	C	D	E	F	G	H	I
2	Range		Count		Value
3	Apple		3		Green	Green	Apple
4	Banana					Yellow	Banana
5	Cherry					Red	Cherry
6	Damson					Purple	Damson
7	Apple
8	Banana
9	Cherry
10	Damson
11	Apple
12	Banana
13	Cherry
14	Damson
Sheet1

Cell Formulas
Range	Formula
E3	E3	=COUNTIF(C3:C14,VLOOKUP(G3,H3:I6,2,0))

 

[Mattymoo]

Its me not explaining thigs correctly.
I have two sheets of data and i am using the below to find out if E2 is on the Budget by Month'!$B$6:$B$250 range. If so it delivers the data and if not it delivers the NOT IN HCSS message.
=IFNA(VLOOKUP(E2,'Budget by Month'!$B$6:$B$250,1,),"NOT IN HCSS")

The above works fine but them on a seperate sheet i want to flag the number of NOT IN HCSS which essentially is an #NA. I need to do it using 2 criterias i.e. how many #NAs in a certain preiod.

=COUNTIFS('Actuals by month'!$C$2:$C$5000,Apr!F3,'Actuals by month'!$Q$2:$Q$5000,Apr!H2) The trouble i have from here is the APR!h2 is the output form the lookup formula above.

 

[Mattymoo]

Its me not explaining thigs correctly.
I have two sheets of data and i am using the below to find out if E2 is on the Budget by Month'!$B$6:$B$250 range. If so it delivers the data and if not it delivers the NOT IN HCSS message.
=IFNA(VLOOKUP(E2,'Budget by Month'!$B$6:$B$250,1,),"NOT IN HCSS")

The above works fine but them on a seperate sheet i want to flag the number of NOT IN HCSS which essentially is an #NA. I need to do it using 2 criterias i.e. how many #NAs in a certain preiod.

=COUNTIFS('Actuals by month'!$C$2:$C$5000,Apr!F3,'Actuals by month'!$Q$2:$Q$5000,Apr!H2) The trouble i have from here is the APR!h2 is the output form the lookup formula above.

and thats where i can't get the count formula talking to the ifna lookup formula.

 

[Skybluekid]

So, on the "Apr" sheet, in H2, you have =IFNA(VLOOKUP(E2,'Budget by Month'!$B$6:$B$250,1,),"NOT IN HCSS"), which will return either a value or "NOT IN HCSS". Then on the "Actuals by month" sheet, you have =COUNTIFS('Actuals by month'!$C$2:$C$5000,Apr!F3,'Actuals by month'!$Q$2:$Q$5000,Apr!H2), where it counts the number of "NOT IN HCSS".

I take it that in Column C is a range of Months, ie January, February, etc, and in Apr!F3 is a given Month? If Column C is Dates, ie, 01/01/2022, then you will have to have to set the upper and lower dates for the month. For example if you want to count the number of "NOT IN HCSS" in April, you will have to use something like

=COUNTIFS('Actuals by month'!$C$2:$C$5000,">="&"01/04/2022",'Actuals by month'!$C$2:$C$5000,"<="&"30/04/2022",'Actuals by month'!$Q$2:$Q$5000,Apr!H2)

Hope I am on the right path

 

[Mattymoo]

Hi, Im really sorry I am obviously having a no brain day. I now have the formula working. I have swapped the =COUNTIFS('Actuals by month'!$C$2:$C$5000,Apr!F3,'Actuals by month'!$Q$2:$Q$5000,Apr!H2) to =COUNTIFS('Actuals by month'!$Q$2:$Q$5000,Apr!H2,'Actuals by month'!$C$2:$C$5000,Apr!F3). I really don't why that would be. Yes you formula above would work also.
Thank you for working this through with me.