Combining Countif and Vlookup

[praveenpatel421983]

I have requirement where the formula should do the Vlookup of the string for the cells in the range and should do countif of the Vlookup results . As I cannot share my project details, I will try to explain with an example using below table:

Fruits/Vegetable	Type
Apple	Fruit
Mango	Fruit
Grapes	Fruit
Orange	Fruit
Banana	Fruit
Carrot	Vegetable
Okra	Vegetable
Egg Plant	Vegetable
Onion	Vegetable
Pumpkin	Vegetable

Export details table:

Column A	Column B	Column C	Column D	Column E	Column F	Column G	Column H
City	1	2	3	4	5	Number of fruits	Number of Vegetables
Delhi	Apple	Grapes	Banana	Okra	Onion
New York	Mango	Grapes	Carrot	Egg Plant	Pumpkin
London	Orange	Banana	Okra	Carrot	Pumpkin

Formula should check whether the string from Column B - Column F is fruit or vegetable and count how many fruits/ how many vegetables are there in the row.
I need formulas for Column G and Column H to get how many fruits and how many vegetables are used in that row. Number of columns are constant. I tried adding extra column for each fruit/vegetable with Vlookup formulas for each of them and countif in the last columns. It works but my number of columns are more than 200 and I have different workbooks with different number of columns in it and it keeps on modifying. Also as the number of rows increases because of so many formulas, size of the file increases. So I was hoping for one formula in a column instead of 200+ columns. Can any one help please.

I am happy even if it can be done using VBA. Please help.
Thanks in advance!

 

[RoryA]

Assuming your first table is on Sheet 2 in range A2:B11 , you can use these formulas in your summary table:

=SUMPRODUCT(--(IFERROR(VLOOKUP(T(IF({1},$B4:$F4)),Sheet2!$A$2:$B$11,2,FALSE),0)="Fruit"))
and
=SUMPRODUCT(--(IFERROR(VLOOKUP(T(IF({1},$B4:$F4)),Sheet2!$A$2:$B$11,2,FALSE),0)="Vegetable"))

then copy down. Adjust row numbers as required (I assumed the formulas start in row 4)

 

[praveenpatel421983]

Assuming your first table is on Sheet 2 in range A2:B11 , you can use these formulas in your summary table:

=SUMPRODUCT(--(IFERROR(VLOOKUP(T(IF({1},$B4:$F4)),Sheet2!$A$2:$B$11,2,FALSE),0)="Fruit"))
and
=SUMPRODUCT(--(IFERROR(VLOOKUP(T(IF({1},$B4:$F4)),Sheet2!$A$2:$B$11,2,FALSE),0)="Vegetable"))

then copy down. Adjust row numbers as required (I assumed the formulas start in row 4)

Wow! works perfectly. Please do me another favor by explaining how the formula works.
Thanks

 

[RoryA]

The T(IF({1} part is a weird construct that is required to force the VLOOKUP to return an array of results, rather than just one. The IFERROR part returns 0 if the lookup value can't be located - it may not be required in your case. The array of VLOOKUP results is then compared to either "Fruit" or "Vegetable", returning True if it matches and False if not. The -- part then converts the TRUE/FALSE values to 1/0 respectively and sumproduct then adds them up to give a count of matching values.

 

[praveenpatel421983]

The T(IF({1} part is a weird construct that is required to force the VLOOKUP to return an array of results, rather than just one. The IFERROR part returns 0 if the lookup value can't be located - it may not be required in your case. The array of VLOOKUP results is then compared to either "Fruit" or "Vegetable", returning True if it matches and False if not. The -- part then converts the TRUE/FALSE values to 1/0 respectively and sumproduct then adds them up to give a count of matching values.

Thanks a lot!

 

[praveenpatel421983]

Assuming your first table is on Sheet 2 in range A2:B11 , you can use these formulas in your summary table:

=SUMPRODUCT(--(IFERROR(VLOOKUP(T(IF({1},$B4:$F4)),Sheet2!$A$2:$B$11,2,FALSE),0)="Fruit"))
and
=SUMPRODUCT(--(IFERROR(VLOOKUP(T(IF({1},$B4:$F4)),Sheet2!$A$2:$B$11,2,FALSE),0)="Vegetable"))

then copy down. Adjust row numbers as required (I assumed the formulas start in row 4)

Hi Rory, Can I convert this formula to VBA code? I used vba to insert the formula directly to the cells but it is increasing the file size by a lot as my data is too big. Please help!

 

[RoryA]

That formula shouldn't increase your file size particularly as it's not that long, but you could have VBA add the formula and then replace it with its value?

 

[praveenpatel421983]

O

That formula shouldn't increase your file size particularly as it's not that long, but you could have VBA add the formula and then replace it with its value?

Ok. Thanks!