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

COUNTIFS - Same criteria across multiple columns?

C

cacos

Member
Hi everyone!


I'm trying to do the following, maybe someone can help. I need to countifs using a single criteria, and the criteria range is made of multiple columns.


For example, if it were possible, it would be something like this:


=COUNTIFS(A:I,O5,J:J,O4)


You'll notice that the 1st criteria range is made of multiple columns instead of a single column.


Thanks!!!
 
Can't be done with countif/s


But can be done with sumproduct

=SUMPRODUCT((A:I=O5)*(J:J=O4))


To speed it up I'd suggest you restrict the ranges like

=SUMPRODUCT((A1:I100=O5)*(J1:J100=O4))
 
One question though, let's say that in a row i have the first criteria appearing several times, and it also meets the criteria from the 2nd condition as proposed, the count will be multiplied. It will return the amount of times both criterias appear, instead of a single "1" as a countifs would do.


For example


O5= Cars

O4= Red


And in a give row I have:

[pre]
Code: 
Cars Trains Cars Cars -- and in the second range for the second criteria "Red"
[/pre]

In that case the count will be equal to 4. How can I make it to just be "1" and ignored the criteria that appear several times?


Hope that was clear, thanks!
 
Hi Cacos,


Try this:


=SUMPRODUCT((SUM(((A1:I100=O5)*1)>=1)*(J1:J100=O4)))


I think the example you have mentioned should give 3 instead of 4, as there are three Cars ( and one train)? Am i correct?


Faseeh
 
Hi Cacos,

You may need to try something like the following:

=SUM((MMULT(N(range1="car"), TRANSPOSE(COLUMN(range1)^0))>=1)*(range2="red"))

enter with Ctrl + Shift + Enter


where range1 is A1:I100, and range2 is J1:J100


Cheers,

Sajan.
 
Last edited by a moderator:
Hi Sajan, could you help me understand the formula? Because I need to do the same, but instead of a COUNTIF, a SUMIF, always against the same criteria ("car" in the example).


The range to sum would be range2 on the example.


I don't fully understand the MMULT but I see it looks like it's creating a matrix and matching against the criteria.


Thanks!
 
Hi cacos,

MMULT does matrix multiplication of two matrices. In this case, it is effectively summing the rows in the matrix created by N(range1="car") so that if "car" is found in a row, the value for that row will be greater than 0.

Then checking if the value returned from MMULT is greater than or equal to 1, we could multiply the second condition (range2="red") to get an array of 1s and 0s which can then be added up to get the number of rows where both conditions are satisfied.


Hope that helps.


Cheers,

Sajan.
 
Hi again!

Went back to using this formula and I'm struggling to apply a "if cell is equal to "All" avoid that column" condition.

Array Formula from Sajan is:

=SUM((MMULT(N(range1="car"), TRANSPOSE(COLUMN(range1)^0))>=1)*(range2="red"))

So I want to add a logical test in the "red" criteria, that basically skips "range2" if the cell that has the "red" value is equal to "All".

Also, i'm adding multiple criterias like the "red" one, so if anyone can think of a faster way of calculating that'd be awesome.

Thanks again everyone!
 
Hi Lucas ,

Basically the portion (range2="red") generates an array of 1s and 0s ; 1 where the cell equals "red" , and 0 otherwise.

If you replace this portion with the following :

(IF(range2="All",1,(range2="red")))

or

(IF(range2="All",1,IF(range2="red",1,0)))

it should work. Since you say it is an array formula , I assume you will be entering the entire formula using CTRL SHIFT ENTER.

Narayan
 
Thanks Narayan, not sure I got it though.

What could equal to "All" is the criteria "red", therefore in that scenario the formula should omit that criteria from the range.

It's what in a COUNTIFS I would do like this: =COUNTIFS(range1,if(criteria1="All","*?",criteria1)

Does it make sense?
 
OK I think I got it! Wasn't thinking it through (happening more and more often these days).

It's: (IF(criteria2="All",1,(range2=criteria2))) -- so that it avoids the whole range directly.

Next goal: making it run faster!
 
Hi dear chandooers, revisiting this MMULT challenge.

I'm trying to find a faster alternative, including other formulae and a vba/pivot table approach, but haven't really nailed it yet. Can anyone think of a better alternative?

The situation is: need to count where a range is made of multiple columns, and the criteria might appear in any of those, even repeatedly. Also, if it does, it should only consider it once per row (despite the fact it's repeated in that row across many columns).

Sajan's formula:

=SUM((MMULT(N(range1="car"), TRANSPOSE(COLUMN(range1)^0))>=1)*(range2="red"))

Also, there are like 10 instances of this formula, therefore the need to reduce calculation time. Each time Excel recalculates it can take a bit (the lookup ranges are extensive, can't be reduced).

Ok, going back to the adventure. Thanks to everyone in advance.
 
You must log in or register to reply here.
Back
Top