#### praveenpatel421983

##### New Member

- Joined
- Aug 17, 2017

- Messages
- 41

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!