Sum all cells meeting condition

**kamelkid2** · 03-04-2011, 08:16 AM

i have a very large spreadsheet so i am trying to find an efficient way to accomplish what i am working on.

in column sz, there will be one of 31 text options that will be inputted down the 1000 rows. what i need to do is a simple statement, non VBA preferably, that will sum all of the cells in column H if the corresponding cell in column sz = "option 1"

i have been able to do it by doing a simple if statement with A LOT of helper columns but this bogs down the system WAY too much as i have approximately 50 columns that i need to do this for each of the 31 options. i did this on a separate worksheet and went past column BAA so i very much need a quicker and more efficient way. anybody have any thoughts?

excel 2007

thank you for any help

**Colin Legg** · 03-04-2011, 08:18 AM

Hi,

Does this give the result you want?

Please Login or Register  to view this content.

If yes and you want to summarise for all 31 text options, you might want to consider using a pivot table which will quickly give a sum of column H for each different value in column SZ.

**kamelkid2** · 03-04-2011, 08:25 AM

yes this suits my needs perfectly. thank you for such quick response too

**kamelkid2** · 03-04-2011, 08:26 AM

how would i go about setting up a pivot table? sorry but i am still relatively new and dont know the more advanced features yet

**Colin Legg** · 03-04-2011, 08:42 AM

Hi,

I searched google for an online tutorial on getting started with Pivot tables in Excel 2007 and this was one of the first hits:
http://www.howtogeek.com/howto/6416/...in-excel-2007/

Plenty more on google if you want to see more.