# Create a countifs formula after a vlookup output



## Mattymoo (Jan 3, 2023)

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 (Jan 3, 2023)

What formula do you have?

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


----------



## Mattymoo (Jan 3, 2023)

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 (Jan 3, 2023)

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
Book1CDEFGHI2RangeCountValue3Apple3GreenGreenApple4BananaYellowBanana5CherryRedCherry6DamsonPurpleDamson7Apple8Banana9Cherry10Damson11Apple12Banana13Cherry14DamsonSheet1Cell FormulasRangeFormulaE3E3=COUNTIF(C3:C14,VLOOKUP(G3,H3:I6,2,0))


----------



## Mattymoo (Jan 3, 2023)

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 (Jan 3, 2023)

Mattymoo said:


> 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")
> 
> ...


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


----------



## Skybluekid (Jan 3, 2023)

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 (Jan 3, 2023)

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.


----------

