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 column

Sometimes (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).

## Another helper column solution

Gary'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:

`=COUNTIFS(\$B\$2:\$B\$14,"="&E\$3,\$C\$2:\$C\$14,"<"&\$E\$2)`

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.