COUNTIFS / SUMPRODUCT Questions

[timeless]

Hello and thank you for your help. I've been racking my brain out on counting the number of instances in both a date range and inventory price range. For example, in column A, there are the days in inventory (e.g., 88, 1, 45, etc.). Then Column B contains the price for the corresponding row cell in A (e.g, $1.50. $28.22, $41.22, etc).

So what I'm attempting to do is number number of items within both a price range and date range (note: our inventory SKUs are unique and each SKU is 1). Example, count the number of items that have been in inventory >=31 Days and <=60 Days that have a Price Range of between >=$1.00 and <=$5.00.

I've tried using COUNTIFS(A:A, ">=31",A:A,"<=60",B:B. ">=1", B:B,"<=5") and out of 214,000 rows, I'm off by ~1300.

I've tried SUM(COUNTIFS(A:A, {>=31","<=60"},B:B,{">=1","<=5"})) and really got a crazy number.

I've tried SUMPRODUCT((A:A>=31)*(A:A<=60),(B:B>=1)*(B:B<=5)) and again, off by ~1300.

I'm wondering if I'm using the ">=" correct or I'm just making a blockhead error. Any insights on this would be most appreciated.

Thank you.

 

[Special-K99]

Try

=SUMPRODUCT(--(A1:A1000>=31)*(A1:A1000<=60)*(B1:B1000>=1)*(B1:B1000<=5))

 

[timeless]

Hi Special-K99! Thanks. It looks like this did it. As I always try to learn, can you advise on the "--" on this array? Also, It appears to work when I put in the actual row numbers (e.g., A1000:A1000) vs just doing A:A. Is that something that's unique to the SUMPRODUCT command?

Again, thank you very much for the help and reply!

 

[AliGW]

This is from an explanation of the double negative that I got when I asked the same question on a different forum:

When you add -- like this


--(B1:B20>=100)

then that "coerces" TRUE to 1 and FALSE to zero so you get an array of 20 1/0 values

In other words, if all of your conditions are met in the formula you've been given, the result will be 1 (or TRUE). If any of them is not met, a 0 will be added to the calculation, which means that the calculation will result in 0 (or FALSE).

Here's a good reference:

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

 

[Aladin Akyurek]

Try

=SUMPRODUCT(--(A1:A1000>=31)*(A1:A1000<=60)*(B1:B1000>=1)*(B1:B1000<=5))

No need for -- if you are using * between the terms.

Hi Special-K99! Thanks. It looks like this did it. As I always try to learn, can you advise on the "--" on this array? Also, It appears to work when I put in the actual row numbers (e.g., A1000:A1000) vs just doing A:A. Is that something that's unique to the SUMPRODUCT command?

Again, thank you very much for the help and reply!

That's no different from:

=COUNTIFS(A:A,">=31",A:A,"<=60",B:B,">=1", B:B,"<=5")

Note. You had a dot instead of a comma as list separator.

This is from an explanation of the double negative that I got when I asked the same question on a different forum:

In other words, if all of your conditions are met in the formula you've been given, the result will be 1 (or TRUE). If any of them is not met, a 0 will be added to the calculation, which means that the calculation will result in 0 (or FALSE).

Here's a good reference:

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

Closer home:
http://www.mrexcel.com/forum/excel-questions/128907-explanation-dashes.html
http://www.mrexcel.com/forum/excel-questions/202204-find-last-cell-data-formular.html
http://www.mrexcel.com/forum/excel-...ria-two-corisponding-columns.html#post1671486

 

[timeless]

Thank you all for your help. It really was appreciated and I learn something new every day.

 

[Aladin Akyurek]

Thank you all for your help. It really was appreciated and I learn something new every day.

You are welcome.