Doing something wrong, formulas counting multiple critera

[Jennifre]

I found this formula which looked like it worked (though I did not check it counting manually to ensure that), which I found on an archived post here to count multiple criteria across several cells:

=SUMPRODUCT((BJ2:BJ1364=3)*(BP2:BP1364=1))

But then, I used the same thing for different cells, and it seemed like it might not be counting accurately, so I checked a count of just one of my criteria using:

=COUNTIF(BJ2:BJ1364,"3")

So, given that I get 0 for the BJ items using the first formula, and 123 items using the countif formula... where is my problem?

As usual, Excel just confuses me. Especially, as I just now realized, the formulas were not set up using any consistent "language," or, say, that you always need to put quotes around what you're checking for (="3" vs =3)... so frustrating to me. I want to learn this, but it just seems randomly designed rather than with a language in mind as a plan, per se.

Thank you so much in advance for your help!! So much... !!

 

[T. Valko]

I found this formula which looked like it worked (though I did not check it counting manually to ensure that), which I found on an archived post here to count multiple criteria across several cells:

=SUMPRODUCT((BJ2:BJ1364=3)*(BP2:BP1364=1))

But then, I used the same thing for different cells, and it seemed like it might not be counting accurately, so I checked a count of just one of my criteria using:

=COUNTIF(BJ2:BJ1364,"3")

So, given that I get 0 for the BJ items using the first formula, and 123 items using the countif formula... where is my problem?

As usual, Excel just confuses me. Especially, as I just now realized, the formulas were not set up using any consistent "language," or, say, that you always need to put quotes around what you're checking for (="3" vs =3)... so frustrating to me. I want to learn this, but it just seems randomly designed rather than with a language in mind as a plan, per se.

Thank you so much in advance for your help!! So much... !!

Yeah, it's kind of confusing, alright!

The 2 functions you've used evaluate numbers differently.

COUNTIF will evaluate TEXT numbers and NUMERIC numbers as being equal but SUMPRODUCT will not.

In COUNTIF 3 and "3" are equal.

In SUMPRODUCT 3 and "3" are not equal.

So, it sounds like the numbers in your range(s) might be TEXT numbers so the SUMPRODUCT formula returns 0.

You can try this:

=SUMPRODUCT(--(BJ2:BJ1364="3"),--(BP2:BP1364="1"))

However, I think you'd be better off "fixing" those TEXT numbers by converting then into real numeric numbers.

 

[Marcelo Branco]

Hi,

What precisely do you want to count?

The SUMPRODUCT formula you have counts only rows that have 3 in column BJ and 1 in column BP.

Are the numbers in these columns formatted as text? If so, they are aligned in the left-side of each cell.

M.

 

[Jennifre]

Biff, thank you!!!

However, of course I'm just more confused now. So... per your recommended formula, what does the -- indicate? Or do?

And, using your formula exactly as written here gives me 0, so likely it doesn't work as written. Am I supposed to substitute something for the --s ?

Is the comma what should be there?

Thanks again. I appreciate your help.

 

[Jennifre]

Hi Marcelo, thank you.

I'm trying to the cells that contain both 3s in the BJ and 1s in the BP column, yet I get 0 as a result of the formula using --s.

No, they seem not to be formatted as text. They're just 0s. Right aligned. Checking, "General" is what I see.

What could be wrong. I wish I could / want to learn/know this myself.

But, thank you -thank you - thank you!!

 

[Haseeb Avarakkan]

Hello, Try this,

=SUMPRODUCT(--(BJ2:BJ1364&""="3"),--(BP2:BP1364&""="1"))

 

[T. Valko]

Biff, thank you!!!

However, of course I'm just more confused now. So... per your recommended formula, what does the -- indicate? Or do?

And, using your formula exactly as written here gives me 0, so likely it doesn't work as written. Am I supposed to substitute something for the --s ?

Is the comma what should be there?

Thanks again. I appreciate your help.

Well, let's first establish that you have a data problem and not a formula problem.

=SUMPRODUCT(--(BJ2:BJ1364="3"),--(BP2:BP1364="1"))

The "--" is known as double unary minus.

Here's how it works...

Each of these expressions will return an array of TRUE and FALSE:

BJ2:BJ1364="3"
BP2:BP1364="1"

Since SUMPRODUCT works with numbers we need to convert those TRUE and FALSE to numbers that SUMPRODUCT can use.

Using the "--" is one way to that:

--(BJ2:BJ1364="3")
--(BP2:BP1364="1")

--TRUE = 1
--FALSE = 0

Once the array expressions are converted to 1s and 0s they are then multiplied together then summed for a result.

See these for more info:

http://xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

However (!), that still doesn't solve your data problem!

See this link. It describes common data problems:

http://contextures.com/xlFunctions02.html#Trouble

 

[Marcelo Branco]

Hi Marcelo, thank you.

I'm trying to the cells that contain both 3s in the BJ and 1s in the BP column, yet I get 0 as a result of the formula using --s.

No, they seem not to be formatted as text. They're just 0s. Right aligned. Checking, "General" is what I see.

What could be wrong. I wish I could / want to learn/know this myself.

But, thank you -thank you - thank you!!

Jennifre,

If they are right-aligned they are probably not formatted as text ( unless you have manually right-aligned the cells).

You are saying that format = General, so Biff's formula should work.

Did you check visually if there is at least one row that satisfy both conditions?

M.

 

[T. Valko]

Hello, Try this,

=SUMPRODUCT(--(BJ2:BJ1364&""="3"),--(BP2:BP1364&""="1"))

You don't want to do that.

Fix the data!

Don't fight with it!

 

[Jennifre]

Hi, thank you Marcelo, Haseeb, Biff.

Marcelo, quickly I fond 3 rows that did what I was hoping to count, so yes some rows exist that would fulfill this set of multiple criteria, hurray! (Also a worry I was having.)

This formula (thank you Haseeb!) looks like it may work. If I erased the second half of it gave me the countif total for column BJ.

However, Biff, your extended note, also thank you!, further confuses me. The data is just standard data that someone pasted in from copying it from... I think it was SPSS. I've not done anything to it, yet. No sorting, no formatting or changing how numerals appear or fonts or anything. Thank you Very Much for the links you included, hopefully/maybe I can get to them. Thank you!!!!

What you have noted, Biff, is that my data is out of whack somehow, just makes me worried. Is there any easy way to check if or how out of whack it may be? Similarly, is there a reason to believe that this formula is not working correctly for me based on things I have written here so far?

Thank you!!