Question: In Microsoft Excel 2003, I have a spreadsheet with data in rows 7 through 268 where Column G has different values (up to 10 different usernames), and column L has different values (up to 11 different codes for different types of orders). What I'm trying to do is have a formula say if G = SMITH, then count how many times the value EM appears in column L and display that number.
So by counting I know that EM appears 2 times in association with username SMITH, but I was hoping I could get a formula to do it so that I can apply it for all usernames.
Answer: Since you want to count the number of occurrences based on 2 conditions (a value in column G and a value in column L), you can do this with an array formula.
Let's look at an example.
First, we have our usernames in column G starting at row 7 and our order types in column L also starting at row 7. We want to count the number of rows where the value in column G is equal to "SMITH" and the corresponding value in column L is equal to "EM". And we want to do this for rows 7 through 268.
In cell N6, we've created the following array formula:
=SUM((G7:G268="SMITH")*(L7:L268="EM"))
When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:
{=SUM((G7:G268="SMITH")*(L7:L268="EM"))}
This formula returns the number of occurrences where the username is "SMITH" and the order type is "EM".
