+ Reply to Thread
Results 1 to 13 of 13

Countifs to be working the same as sumifs function

  1. #1
    Registered User
    Join Date
    03-21-2021
    Location
    Iraq
    MS-Off Ver
    2016
    Posts
    7

    Post Countifs to be working the same as sumifs function

    good day all,

    this thread will be the same as my introduction thread. First of all my name is Saif from Iraq, I work as a planning engineer.

    Well, to get along with it, I saw a lot of excel users looking for a way to count values while to make the function works the same as sumifs.

    bellow I worked on my data depending on date since I need it, I'm working in a factory so I need production quantities on a daily, monthly and yearly basis, the same for getting monthly production batches that has been produced.



    column G has a month value as it's header
    columns L6:BDO6 has dates values as a header
    Production quantities goes from L7:BDO7

    so I need to enter any month value to count produced batches for that whole month

    when I enter "JAN-2021" into G6, count value for January should show up in G7.

    My formula is >>

    =COUNTIFS(L7:BDO7,"<>"&"",$L$6:$BDO$6,">="&$G$6,$L$6:$BDO$6,"<="&EOMONTH($G$6,0))

    as we all see my 1st criteria is I told excel to count values not equal to zero, and my 2nd criteria is that I told excel to start counting only when month value is not less than January and not greater than End of January.

    I hope it'd be useful for you all.

    Cheers,

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,160
    Hello saif Khalid,

    Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    03-21-2021
    Location
    Iraq
    MS-Off Ver
    2016
    Posts
    7

    Re: Countifs to be working the same as sumifs function

    Quote Originally Posted by arlu1201 View Post
    Hello saif Khalid,

    Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.
    Thanks arlu, it's a pleasure to be part of this community.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,946

    Re: Countifs to be working the same as sumifs function

    Just looking at post #1 it appears the the value in G6, JAN-2021, is text.
    So my first suggestion is to make sure that the value in G6 is a date i.e. 1/1/2021
    My second suggestion, in case the first doesn't help, is to utilize the instructions in the banner at the top of the page to upload a sample of the Excel file described in post #1.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    03-21-2021
    Location
    Iraq
    MS-Off Ver
    2016
    Posts
    7

    Re: Countifs to be working the same as sumifs function

    First of all thanks for the explanation, secondly my post wasn't a question I was trying to give help to anyone that needed a formula that counts values of month when month is written, again I really appreciate the your help.

    Cheers.
    Last edited by AliGW; 04-06-2021 at 04:10 AM. Reason: PLEASE don't quote unnecessarily!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,175

    Re: Countifs to be working the same as sumifs function

    You should have posted in the Tips & Tutorials section - I'll move this there now.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Registered User
    Join Date
    03-21-2021
    Location
    Iraq
    MS-Off Ver
    2016
    Posts
    7

    Re: Countifs to be working the same as sumifs function

    Quote Originally Posted by AliGW View Post
    You should have posted in the Tips & Tutorials section - I'll move this there now.
    Well, I posted this thread as an introduction of myself since it was my first post here, anyways all efforts are appreciated Ali.

    Cheers.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,175

    Re: Countifs to be working the same as sumifs function

    My point is, you posted in the wrong place. NOBODY will look for hints and tips in the introduction section, so sharing the tip would be fruitless. I can move it back to where nobody will ever see it if you want.

  9. #9
    Registered User
    Join Date
    03-21-2021
    Location
    Iraq
    MS-Off Ver
    2016
    Posts
    7

    Re: Countifs to be working the same as sumifs function

    :D, I got your point, and I'm thanking you in advance.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,175

    Re: Countifs to be working the same as sumifs function

    In advance of what? You want me to move it back where nobody will ever see it? Are you sure???

  11. #11
    Registered User
    Join Date
    03-21-2021
    Location
    Iraq
    MS-Off Ver
    2016
    Posts
    7

    Re: Countifs to be working the same as sumifs function

    Thank you in advance for moving it into tips and tutorials section.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,175

    Re: Countifs to be working the same as sumifs function

    Ah! It had already been moved when you thanked me in advance for moving it.

    Have a good day!

  13. #13
    Registered User
    Join Date
    03-21-2021
    Location
    Iraq
    MS-Off Ver
    2016
    Posts
    7

    Re: Countifs to be working the same as sumifs function

    :D, well Thanks anyways.

    Cheers.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] COUNTIFS and SUMIFS not working on set Ranges
    By Ochimus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-05-2020, 10:35 AM
  2. Why is the COUNTIFS function not working?
    By jonnyboyb in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-29-2020, 03:04 AM
  3. [SOLVED] Sumifs/Countifs not working across linked workbooks
    By elleb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-13-2018, 05:36 AM
  4. Countifs and Sumifs not working...y?
    By biznez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-18-2016, 06:10 AM
  5. sumifs function not working
    By ammartino44 in forum Excel General
    Replies: 6
    Last Post: 04-16-2015, 12:23 AM
  6. [SOLVED] SUMIFS/COUNTIFS array function question (multiple criteria)
    By akamenov88 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2015, 07:08 PM
  7. COUNTIFS function not working
    By d_mack in forum Excel General
    Replies: 3
    Last Post: 08-26-2010, 02:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1