COUNTIFS; Excluding Columns, And Counting Between Specific Date Range...

[Haslami]

Hey everyone. I have an excel question that is a little beyond my expertise, but I'm hoping to find some help and learn something new as well.

I have a spreadsheet (attached) in which I am trying to total the appearance of certain values, the catch being, I don't want to include all columns within my specified range. My spreadsheet does have a header, I was able to use the header to my advantage (thanks to some wonderful help from this forum), and created a formula that was able to do this.

=COUNTIFS(F17:AYF17,"CAN",F$14:AYF$14,"<>Week")

However, I have run into another obstacle; rather than keeping a running total, I now need to keep a total between certain date range. I added two cells that specify my date range, but I'm not sure how to augment the current formula to only count between this range (if I'm able to do so).

Any help is greatly appreciated.

[63falcondude]

I'm not sure what the expected result of the formula in E17 (assuming that this is the one you are asking about) is but the syntax of the formula is incorrect.

Strictly correcting syntax...

This:
=COUNTIFS(G17:BAL17,"*MOT*",G$15:BAL$15,">E3",G$15:BAL$15,"<D3")+COUNTIFS(G17:BAL17,">0",G$15:BAL$15,"<>Week")

Should look like this:
=COUNTIFS(G17:BAL17,"*MOT*",G$15:BAL$15,">"&E3,G$15:BAL$15,"<"&D3)+COUNTIFS(G17:BAL17,">0",G$15:BAL$15,"<>Week")

[Haslami]

Thank you, that helps tremendously...my count is off though; for some reason the second part of that formula isn't working correctly...I want to add the total number of times "*MOT*" appears with any value above ">0"...it's not recognizing any figure above zero.

Currently: this provides me with a count of 18, should be 19
=COUNTIFS(G17:BAL17,"*MOT*",G$15:BAL$15,">"&$E$3,G$15:BAL$15,"<"&$D$3)+COUNTIFS(G17:BAL17,">0",G$15:BAL$15,">"&$E$3,G$15:BAL$15,"<"&$D$3)

[63falcondude]

If you want to include your start and end dates, you need to adjust the inequalities in your formulas to account for that.

">" should be ">="

and

"<" should be "<="

That being said, I believe that this will suffice:

=SUM(COUNTIFS(G17:BAL17,{"MOT*",">0"},G$15:BAL$15,">="&$E$3,G$15:BAL$15,"<="&$D$3))

[Haslami]

Again, thanks a bunch...you rock!

[leelnich]

withdrawn- already answered

[63falcondude]

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.