• 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.

Sumif or sumproduct: Multiple range for criteria

C

cacos

Member
Hello everyone


I have the following scenario: I need to make a SUMIF, where the criteria might be repeated several times in the criteria range, and the criteria range is made up of multiple columns, let's say A1:N200.


So, it would be (if this was possible) like =sumif(A1:N200,"HOUSE",M1:M200).


It's important that the word HOUSE can appear many times in the same row, and if that happens it should only sum once.


Thanks!
 
Hi, cacos!


For adding with more than condition you can't use SUMIF, you have to use SUMIFS which maybe replaced by SUMPRODUCT depending on the actual case.


As I don't fully understand the constraint in your last sentence (you know the routine, file, upload...), what I could tell you is the syntax for SUMIFS function:

=SUMIFS(sum_range,criteria_1_range,condition_1[,criteria2_range,condition_2...])


Regards!
 
I've uploaded here:


https://docs.google.com/file/d/0B9nov_b3A5SvOEItMG82QlhQREE/edit?usp=sharing


As you'll see the criteria (House) can be in multiple columns, and also appear many times in the same row. In this case, I'd need to only sum once (and not repeat by the amount of times it appears on that row).


Hope that's clearer :)
 
Hi, cacos!


If you may use a helper column (that you can hide if you want to), try this:

a) L2: =SIGNO(CONTAR.SI(A2:J2;"HOUSE")) -----> in english: =SIGN(COUNTIF(A2:J2,"HOUSE"))

and copy down thru L19

b) M1: =SUMA(L2:L19) -----> in english: =SUM(L2:L19)


Regards!
 
HI SirJB! I've tried it but it appears to be counting, instead of summing.


It would need to actually sum in column K, whenever the criteria appears in range A1:K19, and whenever the criteria is repeated in a row, it should only sum once for that row (and not every time the criteria appears).


Thanks!
 
Hi, cacos!

I misunderstood your requirement, please change cell M1 formula to:

=SUMAR.SI(L2:L19;1;K2:K19) -----> in english: =SUMIF(L2:L19,1,K2:K19)

Regards!

PS: BTW, in your uploaded file all the rows contains "HOUSE", it's not a good scenario to test, so check the solution when that condition doesn't happen.
 
Hi, cacos!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

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