Counting Values in a List

[Orbit]

Hey everyone,

I am trying to a multiple criteria countifs, but I need the last one to countif the value is one of several things.

Ex.:

=COUNTIFS($D:$D,">="&H2,'!$D:$D,"<="&H3,$B:$B,{"Green","Blue","Red","Yellow"})

H2 and H3 in this example would be a date range, as column D is a list of dates. Column B would be a list of colors that contain more than just the four above, but I would only want to count them in the row falls between the date range, and the color is on of those colors. I have search the forums and haven't found anything that has given me my answer yet.

Any help is appreciated, thank you in advance.

 

[Eric W]

Just put a SUM around it:

=SUM(COUNTIFS($D:$D,">="&H2,$D:$D,"<="&H3,$B:$B,{"Green","Blue","Red","Yellow"}))

When entering the array of colors, Excel returns an internal array of the results for each individual color. You can add the SUM to get the total. If you don't, you'll just see the first result.

Also, I removed '! in one spot which I assume is the remnants of a sheet name.

 

[Orbit]

Worked perfect. The sad thing is, that I think somewhere in my head I already knew to do that, but for the life of me I could not remember how to do it. Just needed that one final step.

Thanks for the help.

 

[Eric W]

You're welcome.

Don't feel bad about the memory slippage. I'm at the stage of life where every time I learn something new, something else slips out! (Except that darn Gilligan's Island theme. I think that's in there forever!)