Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: COUNTIFS - Is this correct?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2008
    Posts
    304
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default COUNTIFS - Is this correct?

    Hi all,

    Hope you can help. Think i'm doing this correct but isn't working i think because of dates.

    My table is A2 to P5000. I was using =COUNTIFS(E:E,T3,P:P,1)

    Column E:E is dates during the year, Column P:P is number of days which is a calculation between order recieve and order due. T3 is a lookup to show the month I need to get a result for (1 to 12).

    Basically if T3 is 9, i need the formlar to look i E:E for dates in September and count how many say 1 in column P:P

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,671
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: COUNTIFS - Is this correct?

    Are these dates in column E all in one year or are you looking for dates in September of any year?
    Looking for opportunities

  3. #3
    Board Regular
    Join Date
    Jan 2008
    Posts
    304
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS - Is this correct?

    Just one year. I'll do a new workbook each year.

  4. #4
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,671
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: COUNTIFS - Is this correct?

    Ok do you have that year stored anywhere? Or we using the current year?
    Looking for opportunities

  5. #5
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,671
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: COUNTIFS - Is this correct?

    This method uses current year:

    =COUNTIFS(E:E,">="&DATE(YEAR(TODAY()),T3,1),E:E,"<"&DATE(YEAR(TODAY()),T3+1,1),P:P,1)
    Looking for opportunities

  6. #6
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS - Is this correct?

    An alternative method would be

    =SUMPRODUCT((MONTH($E$2:$E$5000)=T3)*($P$2:$P$5000=1))

    Not as efficient as countifs, but with the amount of data that you have it shouldn't be an issue.

  7. #7
    Board Regular
    Join Date
    Jan 2008
    Posts
    304
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS - Is this correct?

    FANTASTIC Works a treat and a massive thanks

  8. #8
    Board Regular
    Join Date
    Jan 2008
    Posts
    304
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS - Is this correct?

    Hi Jason, I tried the Sumproduct this morning but it gives a 0 result. The COUNTIFS worked great.

  9. #9
    Board Regular
    Join Date
    Jan 2008
    Posts
    304
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: COUNTIFS - Is this correct?

    Hi,
    I've done some results to show 1, 2, 3, 4 & 5 days which is great. How do i change it to show anything over 5 days as one result? I tried >5 but won't work.

  10. #10
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,671
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: COUNTIFS - Is this correct?

    Days? You mean months?
    Looking for opportunities

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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