Help with counting two conditionals?

[theliuman]

Need help, can't seem to figure it out for the last few hours I'm not an Excel wiz so hopefully you can help!

Basically, I have two columns.
Column A: Data that has three distinct background colors (green (ColorIndex 43), red (ColorIndex 3), and white/nofill/etc(ColorIndex <> 3 and ColorIndex <> 43)).
Column B: Empty unless an X appears

What I'm trying to do is take three counts, one for each background color, but only count it if there is an X inside the respective row on column B.

I've been racking my brain trying to figure this out but seems like it's tougher than I'd expected. I tried doing a BGColor function to return the BGColor, but then couldn't figure out how to get that function inside of a COUNTIFS.

Any ideas? Thanks!

[DonkeyOte]

Can you outline what (if any) logic determines the background colour in Col A ?
For ex. if you are using Conditional Formatting please detail the rules in place.

In short, if the colour is set arbitrarily then you will require VBA (or use of old XLM calls) and calcs will be open to error.

[theliuman]

No conditional formatting for the background colors in Col A.

Basically, I will just do a manual background fill of the two defined colors (green/red) and then the other 'color' will white/nofill/autoformat. Thanks!!

[DonkeyOte]

I modified my prior post slightly to cover the issues with Manual formatting:

In short, if the colour is set arbitrarily then you will require VBA (or use of old XLM calls) and calcs will be open to error.

Your calcs would be open to error because manually Formatting a cell is not a Volatile action and as such does not invoke calculation

You can pursue a VBA based solution however I would strongly advise you re-think your strategy - ie use values to determine the colour (conditional formatting) and use those same values as the basis for your COUNTIFS function.

[theliuman]

Wow, you're right -- I've been racking my brains trying to figure out something that didn't need to be so complicated!

I broke out another column just detailed the color: green/red/white, and that just solved the issue since now I can use that column to do a comparison in the COUNTIFS.

I was hoping I didn't need to make a new column due to extra work but this way, at least the calculations are spot on. Thanks!

[DonkeyOte]

The point is if you use an additional column you can use the values in that Column to generate the colour in Column A automatically - so you update the additional column only.

For basics re: Conditional Formatting see: http://www.contextures.com/xlCondFormat01.html

[theliuman]

Thanks DonkeyOte, that really helped!

Now here's a toughie -- if I'd like to graph dynamically based on whether Col B contains an "X" or not, is there an easy way to do this? Or would this have to be VBA?

Thanks

[DonkeyOte]

I'd ask you create a new thread (perhaps within Charting Forum) and provide a sample file that illustrates what you have and what you would like to achieve.

We like to keep the threads specific to the question within first post - helps others searching for solutions find similar threads