I receive many questions from readers and when I can, I like to help. Today, I'm going to share solutions to a few reader questions regarding Excel. There's something to learn from each solution.
SUMIF() and helper columnSometimes (often), the reader's working harder than necessary. That was the case with Andrew, who tried to use SUMPRODUCT() to both group categories and multiple each value with each category by a corresponding value. Now, there's more than one way to go when meeting so many requirements, but I tend to reduce the requirement into individual pieces. You can usually come up with a one-formula solution, but is pursuing that route worth your time? Usually, it isn't.
Andrew wants to multiply the BM value by the January value and sum those results by categories - cats, dogs, and so on. Since he's summing and multiplying, SUMPRODUCT() seemed an obvious choice, but it only seems that way. SUMPRODUCT() multiplies corresponding components in a range and returns the sum of those products. There's no easy way to specify criteria - cats, dogs, and so on.
Instead of pursing a one-formula solution, which would take a bit of time and testing, I suggest adding a helper column for the product of the BM and January values. Then, use a SUMIF() matrix that references the helper column. This function sums values in a range that meet criteria using the syntax:
SUMIF(range, criteria, [sumrange])
In this case, range specifies the entire data range, criteria references the matrix header cells (F1:I1) using a relative address, and sumrange is the helper column (column D).
You can learn more about SUMIF() by reading:
- Use wildcards with SUMIF() to create a powerful Excel function,
- Sum only negative values in a given range using SUMIF(), and
- A dynamic SUMIF() function based on naturally occurring data.
Another helper column solutionGary's initial request was for a solution that compares values in columns A and C. When the value in column A is greater than the corresponding value in column C, he wants to change the value in column C to match the value in column A.
At first, I recommended a conditional format that would highlight the appropriate values in column C, but he needs to change the actual value. For example, using the sheet below, the only value in column C that needs updating is the three in C4 - Gary needs a solution that replaces the three with the corresponding one in A4.
Again, I recommended a simpler route - not because it's the best route, but because it's efficient. When considering the comparison, I quickly realized that the MIN() function to return the lesser value in a helper column was an easy solution. Then, Gary can work with the resulting values in the helper column.
This solution might be difficult to fit into an existing sheet - it just depends on how many dependent formulas require updating. It's a possibility, but not the only solution.
COUNTIFS()Shekar didn't need a simpler solution; he needed help applying the right function - that happens to me all the time! Shekar needed a function to count the number of records in each fruit category with an Aging value of less than three.
COUNTIFS() can handle this, but getting the criteria expressed correctly is tricky. This function counts the number of entries in a range that meet criteria using the following form:
COUNTIFS(criteriarange1, criteria1, [...criteriarange2, criteria2...])
I created a COUNTIFS() matrix and used concatenation to create the criteria arguments in the following formula:
In a nutshell, this function counts only the records where the value in column B is equal to the value in E3 and the value in column C is less than the value in E2.
To learn more about COUNTIFS(), read:
- Use COUNTIFS() to evaluate multiple count and criteria ranges,
- Use COUNTIFS() to compare two data sets in Excel, and
- Count the number of Excel records that fall between two dates.
Answering readers' questions
I enjoy helping when I can, but replying to readers is a free enterprise for me. (If you can't read between the lines, that means I answer questions sent to me privately, for free.) In addition, you might be surprised to learn how few of the solutions I send to readers privately end up as tips. This week just happened to be a lucky exception for me. You're free to contact me, but also consider asking for help in the tip's comment section. You might receive a quicker response and it might be more efficient than mine. In addition, doing so opens the discussion to all readers, and we can all benefit from learning something new. If you have a different solution for one of these Excel problems, please share it in the comments section.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.