I am trying to do a summation of data in a column that has 3 conditions.

First I need to sum the heights in column O that is blue, red, or green (column q), and are greater than 10, less than 30. (column o)

example

green 10
blue 12
red 19
blue 39
blue 12
green 3
red 7
blue 7
red 12
red 76
blue 4

Question that I am asking excel, for all "blue", what is the total sum of height that is greater than 10 and less than 30?

Answer should be: 24

Can anyone help? I tried to use the conditional sum wizard and all it would do is count the number of blue that were between 10 and 30, but it wouldnt actually sum the heights for me.

Assuming your table is in F43:G43, this array formula should work.

After you enter it in the formula bar, you must use Ctrl, Shift, Enter to create the array formula. The formula will end up with {} around it.

=SUM(IF($F$43:$F$53="blue",IF($G$43:$G$53>=10,IF($G$43:$G$53<=30,$G$43:$G$53,0),0),0))

This little macro will do it .
15 is the number of the column O , 17 is the number of column P
Rowindex 3 to 13 means the data starts in row 3 in my example and ends in row 13 but you can change this to however many rows of data you have.
It displays the sum in cell P1

Hope it helps
---------------

Public Sub anyname()
i = 0

For RowIndex = 3 To 13

If (Cells(RowIndex, 17) = "red" And _
(10 < Cells(RowIndex, 15)) _
And (Cells(RowIndex, 15) < 30)) Then
i = i + Cells(RowIndex, 15)
End If

Next

Range("P1") = i

End Sub

If you're not totally confused , you don't fully understand the question.

thank you very much for both of your help, i tried the formula and it returned An error, that looked like #num!

i was trying to avoid doing a macro, but i will try it and see what happens, but thank you very much

Sorry, but I used 10 and 30 inclusive in my array formula. The change is simple:

=SUM(IF($F$43:$F$52="blue",IF($G$43:$G$52>10,IF($G$43:$G$52<30,$G$43:$G$52,0),0),0))

Ctrl, Shift, Enter

its still giving a #num! error, do i need to use some sort of concatenate function since it is looking for the word "blue"

Not sure what you did with the formula, but I just tried it again and it worked fine.

Obviously you need to change the ranges that the formula refers to and you have to enter it by using Ctrl, Shift, Enter

Using the formula submitted in Response Number 4, these are my results:

Blue - 24
Green - 0
Red - 31

Works for me!

well, what i am actually trying to calcualte is about 200 cells long,and do not lie in 2 consecutive columns, its column o and column q, oh not all the cells with colors have data, some of them are blank, do you think these factors are playing a role in this error message?

The formula works *as is* for colors in F43:F52 and numbers in G43:G52.

Why would you need a concatenate function to look for the word "blue"?

i dont know, im just trying to figure out why i keep getting this error message

I don't think any of these conditions would cause a #NUM! error. Look up the #NUM! error in help and see if any of the causes fit your situation.

Do us a favor: Copy the data that you gave in you original post in F43:G52 and try my formula. If it stills gives you a #NUM! error, something is very bizarre.

I can PM you with my email address, and you can send me your worksheet, but I won't be able to look at until after 5:00PM EST. (Can't access that e-mail address from work) Let me know.

OMG Im sooooooooo thankful and excited, i finally got it to work, thank you soooooooooooooooooooooooooooooooo much!!!!! (sorry for being overly exicted!!!) BUt im very greatful!

Another option, that is not an array formula is this:

=SUMPRODUCT((F42:F52="blue")*(G42:G52>10)*(G42:G52<30)*(G42:G52))

This formula works on the individual ranges as follows:

F42:F52="blue" is evaluated to be:
FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE

G42:G52>10 is evaluated to be:
FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE

G42:G52<30 is evaluated to be:
TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE

G42:G52 is evaluated to be:
10;12;19;39;12;3;7;7;12;76;4

Excel will then convert all the TRUE's to 1 and FALSE's to 0. It will then multiply all 4 of the individual elements and sum the products. For example, the first data in the list is Green 10, so per the criteria, the result is:
FALSE*FALSE*TRUE*10 or 0*0*1*10 which equals 0.

To see this in action, highlight a section of the formula, such as F42:F52="blue" and hit F9.