• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum for multiple criteria - SUMIF formula doesn't work! as array either

Pablo

Pablo

New Member
Hello,
I have a report for different units, and based on a list or criteria, I need to add the values for only those units.
How can I get a formula that will work with a regular list (no empty rows to match the rows)?

I got it to work with an array formula, "SUM(IF(...", but only when the criteria is in the same row as the units, I don't understand why. Curiously SUMIF doesn't work at all.
Also I got it to work with Sumproduct using {...,...,...} which is like an OR, but only when I type the criteria in double quotes, so it doesn't work with cell references, which is very inconvenient since it can't be changed / updated easily. I don't get that either.

I have attached the file as reference. Any help is appreciated.
Thanks,
Pablo
 

Attachments

  • Add values based on multiple criteria.xlsx
    10.2 KB · Views: 11
Hi Narayan,
Thanks for such a quick response. Impressive.
It works great. Could you please explain why do I need to use Transpose and how does it work?
Also, why I can't use cell reference inside the curve brackets? {...}
Thanks again.
Pablo
 
Hi Pablo ,

The TRANSPOSE is required when you are trying to compare two arrays , since what you want is not a one-to-one comparison ; you want the first cell in the data range to be compared to all the cells in the criteria range , then the next cell in the data range should be compared to all the cells in the criteria range , and so on. Using the TRANSPOSE does exactly this. TRANSPOSE converts a row vector to a column vector and vice-versa , so that what results is a matrix of 0s and 1s ; thus if your data range has 20 rows , and the criteria range has 4 rows , we will end up with a matrix of 20 rows and 4 columns , where the 4 columns are the TRANSPOSE of the 4 rows of the criteria range.

Thus each cell in the data range results in a row of 4 values.

The {} brackets are used to specify an array of literals , either numeric or text. Literals are values which are specified directly , not as cell references.

However , I think both the SUMIF and the SUMPRODUCT versions will work if the formula is written the way it should be written ; let us wait and see.

Narayan
 
You must log in or register to reply here.
Back
Top