i'm really having trouble understanding all this array stuff
i want the excel sheet to count all the values in B2:b10 that are "m" AND c2:c10 are "a" and report 1 number to me. So that i can say, this many boys had A's
This will check the column A1 through A6 for instances of "M" and then total up all instances of grade "A" that correspond with the number of "M"s.
All you then need to do is copy this formula (remembering to hit CRTL-SHIFT-ENTER to get the curly brackets (you can't just type then in)) for each instance you are checking for, i.e. grade B, C and D and the same formulas, but to check for females "F".
Thats true, you cannot pass wildcards in Excel. I am sure there is a more elegant way to achieve what you want, but if you are only searching for 6 characters (ZCA<SPACE>MV = 6 chars), AND all the case numbers in your example start with ZCA, then you could use the following:
In the above formula, I am testing the first 6 characters, that are left most in the column A to see if they equal "zca<space>mv". If they do, then I test the corresponding cell entry in column B. If it equals "approved", then I sum it.
IN your example, the answer comes out as 2, so it works!
Incidentally, the formula above is an array formula again, so be aware of the CRTL-SHIFT-ENTER process.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.