Trouble with array formula, countif w/ multiple criteria examples provided

[TXNCPO]

Good Morning,

I am attempting to get the follwing to work, not much above novice user but I try, not sure this is even possible, working formula and purpose at bottom of post.

Couple of failed attempts, based on help on simpler issues and internet searches.

=SUM(COUNTIFS($A$10:$A$58, ("GREEN","N/A","UNKNOWN","YELLOW","RED")) + COUNTIFS($B$11:$B$58, ("GREEN","N/A","UNKNOWN","YELLOW","RED")))



=SUM(COUNTIFS(A10:A58,("GREEN","N/A","UNKNOWN","YELLOW","RED"), B11:B58,("GREEN","N/A","UNKNOWN","YELLOW","RED"))


I cut/paste from the notepad doc i'm building the formula in and then Cntrl +Shift + Enter.

I have about 3 other permitations of failed attempts, but you may get the jist.


PURPOSE

I am counting by word (i.e. GREEN) as opposed to color, all instances of these 5 words as a denomenator (bottom number) with the numerator count being GREEN, N/A, UNKNOWN to give me a readiness percentage. The formulas work, just trying to expand knowledge and see if a more better way to approach.


I have gotten the count to work with the formula below to give result i'm looking for

NUMERATOR in cell G3

=COUNTIF($A$10:$A$72,"GREEN") + COUNTIF($A$10:$A$72,"N/A") + COUNTIF($A$10:$A$72,"UNKNOWN") + COUNTIF($B$11:$B$57,"GREEN") + COUNTIF($B$11:$B$57,"N/A") + COUNTIF($A$10:$A$72,"UNKNOWN")



DENOMENATOR in cell G4 (counting all 5 words)

=COUNTIF($A$10:$A$58,"GREEN") + COUNTIF($A$10:$A$58,"N/A") + COUNTIF($A$10:$A$58,"UNKNOWN") + COUNTIF($A$10:$A$58,"YELLOW") + COUNTIF($A$10:$A$58,"RED") + COUNTIF($B$11:$B$58,"GREEN") + COUNTIF($B$11:$B$58,"N/A") + COUNTIF($A$10:$A$58,"UNKNOWN") + COUNTIF($B$11:$B$58,"YELLOW") + COUNTIF($B$11:$B$58,"RED")



formula in cell to give percentage.

=G3/G4


Thank you

R/ Joe

 

[Fluff]

Untested but try
=SUM(COUNTIFS($A$10:$A$58, {"GREEN","N/A","UNKNOWN","YELLOW","RED"}) + COUNTIFS($B$11:$B$58, {"GREEN","N/A","UNKNOWN","YELLOW","RED"}))
This is a normal formula, not an array formula

 

[BlueHornet]

I'm not at all sure about what you're trying to finally achieve (this is why sample data can be helpful, along with "expected result"), but try this format to the formula:

=SUM( COUNTIF( A:A, {"Green","NA","Red","Yellow"})) + SUM( COUNTIF( B:B, {"Black","Brown","Unknown"}))

 

[TXNCPO]

Fluff,

worked purfectly, was even able to simply delete out yellow and red to get other number.

Thank you.

Now to go back and figure out what I was doing wrong. I think I got hung up on Sqiggly brackets = array

 

[Fluff]

You're welcome & thanks for the feedback