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

CountIf or SumProduct... I don't know...

P

Parts Guy

New Member
Hi,


I have a range of part numbers that have current sales in column B. The current On-Hand (O.H.) is in column C.


I am trying to count the number of Part Numbers that have "0" O.H. only if they have a yearly sale of between 4 and 12.


So in my head, here is the formula. Count the "0"'s in column "C" if the Sales from B1:B6 are >4 and <12. Here is an an example below.

[pre]
Code: 
Part Number, YRSL, O.H.
123,          13,    1
124,           9,    0
125,           8,    0
126,           5,    1
127,           5,    0
128,           3,    0
[/pre]
In this example the answer is "3". There are 3 part numbers (124,125,127) that have sold between 4 and 12 times that have a current O.H. of "0".


I have a sample file I can send if necessary.


Thanks,


Jeff
 
Hi, Parts Guy!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


Try COUNTIFS function:

=CONTAR.SI.CONJUNTO(B2:B7;">4";B2:B7;"<12";C2:C7;0) -----> in english: =COUNTIFS(B2:B7,">4",B2:B7,"<12",C2:C7,0)


Regards!
 
If you have XL 2007+

=COUNTIFS(C2:C7,0,B2:B7,">=4",B2:B7,"<=12")

or you can do:

=SUMPRODUCT((C2:C7=0)*(B2:B7>=4)*(B2:B7<=12))
 
@Luke M

Hi!

If I have to choose between a non-volatile and a volatile function I'll surely choose the first one.

Regards!
 
@SirJB7

Indeed...hence the first line of my post started with an "If". The first is preferred, but isn't available to XL2003 or older.


PS. Yes, you could do triple-level embedded SUM/IF formula, which would not be volatile, but that gets pretty messy. =P
 
@ bobhc..


Little modification.. :)


=COUNTIFS($C2:$C7,0,$B$2:$B$7,{">=4","<=12"})


However.. When you are using COUNTIFS.. no need to make it more complex.. :)


Regards,

Deb
 
@b(ut)ob(ut)hc

Hi, my friend!

Too much Carlsberg today or just missed a "B2:B7," between the 4 and 12 conditions?

Regards!


EDITED

PS: Debraj Roy's got my point.
 
Thanks for the feedback and the answer to my question!


I searched this site for days before posting this request.


Thanks again,


Jeff
 
Thanks Luke..

but Countif(range,{condition1,Condition2...}) is only applicable in Single range.. multiple condition..


Regards,

Deb
 
Hi Deb,


I'm not quite following the suggestion of using an array constant....

Code: 
=COUNTIFS($C2:$C7,0,$B$2:$B$7,{">=4","<=12"})


returns a 2 element array so you need a [code]SUM() wrapper:

[code]=SUM(COUNTIFS(C2:C7,0,B2:B7,{">=4","<=12"}))


But, when you use an array constant in this situation, it imposes an OR condition on the range B2:B7 (greater than or equal to 4 OR less than or equal to twelve), so it isn't suitable in this case. I think SirJB/Luke's idea of COUNTIFS()[/code] with AND conditions is the way to go:


=COUNTIFS(C2:C7,0,B2:B7,">=4",B2:B7,"<=12")[/code]


Hi SirJB,


Neither of Luke's formulas in post #3 look volatile to me?
 
@Colin Legg


Hi!


Well, I don't know what to say so let me go to the facts:


a) I always had SUMPRODUCT function as a volatile function which despite of it powerful capabilities it should be avoided with huge sets of data or with multiples instances of it.


b) I like and use SUMIFS and COUNTIFS and all other *IFS whenever it's possible.


c) I always thought that volatile functions should have been only those for which Excel needs to recalculate them at each change, either because of it dependent references changed or it intrinsic values did (RAND, NOW, ... but not OFFSET), but as Redmond Guys used and use to not being always predictable I accepted the MS definition of volatile and non-volatile functions.


d) So when Luke M wrote the SUMPRODUCT function and as I didn't see the starting If that he correctly mentioned later, that was why I posted my comment about volatility.


e) Now reading your last comment, I thought "and if this guy is right? let me check it", then I remembered a workbook for testing volatility and I found this links chain:

http://chandoo.org/forums/topic/calculation-speed-slows-down-with-sumproduct-formulas

http://chandoo.org/forums/topic/calculation-speed-slows-down-with-sumproduct-formulas#post-840

http://www.decisionmodels.com/calcsecretsi.htm

http://www.decisionmodels.com/Downloads/VolatileFuncs.zip

from where I extracted, modified to test this case and uploaded this fixed file:

https://dl.dropboxusercontent.com/u/60558749/VolatileFuncs%20%28for%20Colin%20Legg%20at%20chandoo.org%29.xlsm

I only added rows 43:49 to benchmark COUNTIFS vs SUMPRODUCT


Conclusion:

1) As per the file neither SUMPRODUCT nor COUNTIFS functions are volatile.

2) Distrusting and looking for an official MS confirmation about SUMPRODUCT volatility, I found nothing either exhaustive or doubtless except for these links:

http://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx

http://msdn.microsoft.com/en-us/library/office/ff726673(v=office.14).aspx


At last, I'm still wondering if it is volatile or non-volatile. Time will tell, I guess.


Regards!
 
Hi SirJB,


I agree that
Code: 
SUMPRODUCT() is not volatile.

I also agree that I prefer to use [code]*IFS() whenever possible instead of SUMPRODUCT()
because of calculation overhead and simplicity.


The irony is that it is actually possible to make the *IFS[/code] functions volatile if you misuse them (!!), which is why I wanted to check with you what you had meant in post #4. :)


Have a nice evening,

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