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:
Export details table:
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!
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!