Combining Countif and Vlookup

praveenpatel421983

New Member
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.

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

RoryA

MrExcel MVP, Moderator
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

New Member
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

MrExcel MVP, Moderator
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

New Member

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

New Member
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

MrExcel MVP, Moderator
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

New Member
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!

Replies
4
Views
321
Replies
1
Views
133
Replies
12
Views
399
Replies
7
Views
104
Replies
1
Views
554 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

1,152,115
Messages
5,768,194
Members
425,459
Latest member
Danniey We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back