How put OR into multiconditional SUMIFS (and AVERAGEIFS, MEDIAN)?

[somanyqs]

I think I could do a simple sum of these two:
=SUMIFS(A1:A10,B1:B10,"="&"Red",C1:C10,"="&"2013")
=SUMIFS(A1:A10,B1:B10,"="&"Blue",C1:C10,"="&"2013")

But wondering if there is a way to embed the OR into the SUMIFS.
Like (I can't get this to work though..)
=SUMIFS(A1:A10,B1:B10,OR("="&"Blue","="&"Red"),C1:C10,"="&"2013")


And actually have the same question for getting the
AVERAGEIFS
and
MEDIAN
for A1:A10

Much appreciated-

 

[pgc01]

Hi

Try:

=SUM(SUMIFS(A1:A10,B1:B10,{"Red","Blue"},C1:C10,2013))

 

[somanyqs]

That works great.
I probably should have asked in a more general way also...I keep forgetting to do that!
If instead of cells with "Red" and "Blue", would you know how to do this to reference cells instead?
So instead of "Red" I would want to reference cell D1, and users could type Red or Green into that cell, and instead of "Blue" I would reference cell E1, and users could type in Blue or Yellow (I tried simply substituting the cell reference but no luck...)
Thank you!

 

[barry houdini]

If you have a range of cells containing the colours you want to include, e.g. D1:D2 then try this version

=SUMPRODUCT(SUMIFS(A1:A10,B1:B10,D1:D2,C1:C10,2013))

 

[somanyqs]

barry-
Thanks for that idea
Not so much a range that I wanted to include, but rather just a few cells cell that people would type in different text on the fly. So I don't think I could use a fixed range (I'm going to think about that, though...).

 

[somanyqs]

Hi

Try:

=SUM(SUMIFS(A1:A10,B1:B10,{"Red","Blue"},C1:C10,2013))

Thanks, that helps to start. Is there also an option for not Red and not Blue?
Say, if D1 is not red and if E1 is not blue?
Something like
=SUM(SUMIFS(A1:A10,B1:B10,"<>"&{"Red","Blue"},C1:C10,2013))
Though I know that formula doesn't work

 

[barry houdini]

For neither red nor blue you can do that with simple criteria like

=SUMIFS(A1:A10,B1:B10,"<>Red",B1:B10,"<>Blue",C1:C10,2013)

 

[somanyqs]

Aw! Now THAT one I should have been able to get on my own!! :D
Thanks!