countif formula for multiple conditions in the same coloumn

Hi All,

I want to count the number of "a"s and "b"s in column 1 if the relevant column 2 value is equal to "aa" or "bb". please refer below data set. Answer should be 4.

Here is the formula i used for this,

=SUMPRODUCT(COUNTIFS(E16:E25,{"aa","bb"},C16:C25,OR("a","b")))

a - aa
d - aa
b - bb
b - aa
c - cc
b - dd
a - ee

Hi.

=SUM(COUNTIFS(E16:E25,{"aa","bb"},C16:C25,{"a";"b"}))

See here for an explanation on this syntax if you're interested:

http://excelxor.com/2014/09/02/count...iteria_ranges/

Regards

as per above data the answer will be 3

4 as result? why?

Formula:

Please Login or Register  to view this content.

gives 3 as result and XOR LX's formula gives 2 as result.

Please Login or Register  to view this content.

Another working formula, which give result 3.

Originally Posted by Fotis1991

4 as result? why?

Formula:

Please Login or Register  to view this content.

gives 3 as result and XOR LX's formula gives 2 as result.

XOR LX's will also giving the same result as your formula i,e 3

@nflsales

So in that case it's obviously that i am missing something. What is this?

Originally Posted by Fotis1991

@nflsales

So in that case it's obviously that i am missing something. What is this?

=SUM(COUNTIFS(E16:E25,{"aa","bb"},C16:C25,{"a";"b"}))

instead of ;

Hi kazun909, you mean this?

Please Login or Register  to view this content.

Originally Posted by Fotis1991

4 as result? why?

Formula:

Please Login or Register  to view this content.

gives 3 as result and XOR LX's formula gives 2 as result.

Not sure I understand, Fotis.

My formula also gives 3. Did you copy it correctly?

Edit: just seen wenqq3's post #12. Shame on you, Fotis, for not copying the formula correctly! You should read the post in my blog to understand this syntax business!



Regards

As ALL of you say this, i have no reason to don't believe it(also when i saw carefully it, i know how it is works and also i know that you test your formulas before you post ), BUT i am very curious why does not gives me correct result..

Take a look to my attachment in post#10.

Originally Posted by Fotis1991

As ALL of you say this, i have no reason to don't believe it(also when i saw carefully it, i know how it is works and also i know that you test your formulas before you post ), BUT i am very curious why does not gives me correct result..

Take a look to my attachment in post#10.

What doesn't give the correct result? I don't understand.

In that attachment you did not copy my formula correctly, as wenqq3 pointed out in post #12.

Do you mean you don't understand why it doesn't work if you have semi-colons in both arrays? If so, then like I said, I would suggest you have a read of my explanation here!

http://excelxor.com/2014/09/02/count...iteria_ranges/

Cheers

Do you see the formula in F2 in my attachment?? Gives 2 as result..

I say again that i don't have any objection that formula works.

My goal is to find out why does not works for me.

Originally Posted by Fotis1991

Do you see the formula in F2 in my attachment?? Gives 2 as result..

I say again that i don't have any objection that formula works.

My goal is to find out why does not works for me.

The explanation is in the link I sent you.

If you don't want to read all that then I understand. I could try to explain again here in this post but it would take some space and time and since I've already written it there it may be best if you spend some time reading that!

Cheers

Originally Posted by Fotis1991

Do you see the formula in F2 in my attachment?? Gives 2 as result..

I say again that i don't have any objection that formula works.

My goal is to find out why does not works for me.

F2 formula have typo.

=SUM(COUNTIFS(B1:B7,{"aa";"bb"},A1:A7,{"a";"b"}))

Where XOR LX is
=SUM(COUNTIFS(B1:B7,{"aa","bb"},A1:A7,{"a";"b"}))

...I've already written it there it may be best if you spend some time reading that!

As you can see i have(since many days now) a link of your site in my Signature. So no need of such suggestions. I have read it again and again and as i believe that i am not an idiot not even an Excel idiot..) i try to say that formula works ONLY if i put in both ARRAYS semi colons(we use semi colons in Greece as separators. Not comma) and gives a result of 2. Trying to use comma in one ARRAY and semi colon in the other, gives me an error.

I assume that it's something from my local settings...

Thank you for your replies.

@ wenqq3

Thank you very much for your explanations. I really appreciate this.

Originally Posted by Fotis1991

As you can see i have(since many days now) a link of your site in my Signature.

Very kind of you!

Originally Posted by Fotis1991

Trying to use comma in one ARRAY and semi colon in the other, gives me an error.
I assume that it's something from my local settings...

I recall that for the Excel version you're using you do have a different syntax for array constants, though I thought that only applied to arrays containg numerical values.

I can't remember the exact syntax, but I think "\" is used in those cases. Am I right?

Regards

Originally Posted by XOR LX

Very kind of you!

I can't remember the exact syntax, but I think "\" is used in those cases. Am I right?

Regards

Yes you are! Thank you.So the Greek version of your formula is:

Formula:

Please Login or Register  to view this content.

Originally Posted by Fotis1991

Yes you are! Thank you.So the Greek version of your formula is:

Formula:

Please Login or Register  to view this content.

Great! Remember it!!

I'll do my best...My age sometimes does not helps..........

Originally Posted by Fotis1991

I'll do my best...My age sometimes does not helps..........

I thought Greeks were eternally youthful?

Originally Posted by XOR LX

I thought Greeks were eternally youthful?

Greeks were eternally youthful!

I doubt if we are any more........

Difficult situations..They f...@@@ us every day......