How to count one range using multiple criteria

[zadago6311]

I'm trying to compare two different credit rating systems. One rates stocks Aaa, Aa1, Aa2, Aa3, A1....and so on. The other rates things IG1, IG2.... I have the stocks in column A the first ratings in column B and the second rating in column C. I want to count how many stocks are listed as Baa3 or HIGHER in column B but LOWER than IG10 in column C. Is there a way to do this?

[quekbc]

Would something like this work for you?

EDIT: What I've done is
1) Created a list of credit ratings based on ranking (one for Moody's and the other for IG)
2) Compare them using MATCH functions.

[zadago6311]

Wow that's great thanks!

[FDibbins]

quekbc, we are trying to encourage members to include their suggested formula/s in their post, so other members don't need to upload your file, just to see what you did

[quekbc]

Fairly noted, Ford. It would have been a fairly long process to write everything down and I thought it would be best shown in a spreadsheet + I was feeling lazy earlier today - need more coffee.

[FDibbins]

I was feeling lazy earlier today - need more coffee.

haha been there done that. Just saying that if it is a short-ish formula, add it to your post, along with the file. Wont always be practical, but it often helps

[zadago6311]

Just curious, What is the "--" for in the formula?

[quekbc]

Please Login or Register  to view this content.

This is to deal with the TRUE/FALSE values within the first parenthesis of the --, that is... the formula MATCH(B1:B5,$K$2:$K$24,0)<MATCH(G2,$K$2:$K$24,0) results in an array of TRUEs and FALSEs, (e.g. {FALSE, FALSE, TRUE, TRUE, FALSE})
The first negative sign (-) converts the TRUEs and FALSEs to -1s and 0s. (i.e. {0, 0, -1, -1, 0}). The second "-" converts it back to positive (i.e. {0, 0, 1, 1, 0}) so I can use it with a SUMPRODUCT to only SUM the numbers that I want.

You'll see variants of these here. I use --, some use 0+, some use 1*. All these does pretty much the same thing, converting TRUEs and FALSEs to 1s and 0s.