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

Help with COUNTIFS

T

Tony C

New Member
Hello -

I was under the impression that COUNTIFS work the same way as SUMIFS but I think I'm wrong because SUMIFS start with a SUMRANGE, then the criteria, where the COUNTIFS statement does not begin with a COUNTRANGE but rather the criteria...


Here's an example of what I'm trying to do:

Cells B2, B3, and B4 are user defined.

With the values entered in these cels, I want to count how many times a cell value ends with the word "Forgot". I know I can use wildcards for that, but it's the criteria I'm struggling with.

Basically, in this example, I want it to tell me how many times "Forgot" shows in in columns E and F, when column D matches the input in B2 and when the date in Column G is equal to or greater than the Start Date in B3, but less than or equal to the End Date in B4.

I have highlighted the instances where that should be counted in this example... so I would want it to return a value of "3", because those instances of "Forgot" occured in store 341, between the dates of 1/8 and 1/20.


6hp669.jpg


And thanks again in advance for the help from the community here.

I'm working on a pretty cool project to unviel at a meeting next week. I know what all I want it to do, but getting it to do it is the hard part with the limited knowledge of excel that I have. I'm kind of learning as I go.
 
Hi, Tony C!
Do you want to count or to highlight? If count, for each column or for both columns together?
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you, relieving the contributor of having to build a test file, if necessary. Thank you.
Regards!
 
Hi Tony ,

Something like this ?

=SUMPRODUCT(($D$5:$D$20=$B$2)*($G$5:$G$20>=$B$3)*($G$5:$G$20<=$B$4)*(ISNUMBER(SEARCH("Forgot",$E$5:$F$20))))

Narayan
 
  • Like
Reactions: GFC
@Tony C...

...And here is the formula with countifs:

=COUNTIFS($D:$D,$B$2,$G:$G,">="&$B$3,$G:$G,"<="&$B$4,$E:$E,"*Forgot*")+COUNTIFS($D:$D,$B$2,$G:$G,">="&$B$3,$G:$G,"<="&$B$4,$F:$F,"*Forgot*")

Regards!!
 
AIM said:
@Tony C...

...And here is the formula with countifs:

=COUNTIFS($D:$D,$B$2,$G:$G,">="&$B$3,$G:$G,"<="&$B$4,$E:$E,"*Forgot*")+COUNTIFS($D:$D,$B$2,$G:$G,">="&$B$3,$G:$G,"<="&$B$4,$F:$F,"*Forgot*")

Regards!!
Click to expand...

Thank you!

Question...

would =COUNTIFS($D:$D,$B$2,$G:$G,">="&$B$3,$G:$G,"<="&$B$4,$E:$F,"*Forgot*") work as well?... or can I not have it do a range of E:F?

Thank you again for your help :)
 
TonyC

Narayan's approach will give you the formula without adding two formulas together the way you are trying to do it. Use that if you want a more elegant solution.

Take care

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