Combining Countif and Vlookup

praveenpatel421983

New Member
Joined
Aug 17, 2017
Messages
41
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/VegetableType
AppleFruit
MangoFruit
GrapesFruit
OrangeFruit
BananaFruit
CarrotVegetable
OkraVegetable
Egg PlantVegetable
OnionVegetable
PumpkinVegetable

Export details table:
Column AColumn BColumn CColumn DColumn EColumn FColumn GColumn H
City12345Number of fruitsNumber of Vegetables
DelhiAppleGrapesBananaOkraOnion
New YorkMangoGrapesCarrotEgg PlantPumpkin
LondonOrangeBananaOkraCarrotPumpkin

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!
 

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
Joined
May 2, 2008
Messages
38,094
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Aug 17, 2017
Messages
41
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
Joined
May 2, 2008
Messages
38,094
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Aug 17, 2017
Messages
41

ADVERTISEMENT

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
Joined
Aug 17, 2017
Messages
41
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
Joined
May 2, 2008
Messages
38,094
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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?
 
Master Excel Bundle

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

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top