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?

=SUMIF(SZ:SZ,"option 1",H:H)

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.