Two conditions must be true to be counted.
Results 1 to 3 of 3

Thread: Two conditions must be true to be counted.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Two conditions must be true to be counted.

    I'm working on a spreadsheet that two conditions have to true in order to be counted. If the month is January, and the number next to it is less than or equal to 30, then it's counted. Same rule applies for all the other months.
    Thus far, I have:

    =COUNTIFS(Sheet1!D2:D7,(SUMPRODUCT(--(MONTH(D2:D7)=1))),Sheet1!E2:E7,(COUNTIFS(E2:E7,"<=30"))) For example:

    Column D Jan 1, 2014 Feb 3, 2014 Feb 16, 2014 Mar 5, 2014 Mar 13, 2014 Mar 29, 2014 Column E 37 25 30 31 1 16 Outcome Jan 0 Feb 2 Mar 2

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,059
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Two conditions must be true to be counted.

    Try this for January.

    =SUMPRODUCT((MONTH(Sheet1!$D$2:$D$7)=1)*(Sheet1!$E$2:$E$7<=30))
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Aug 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Two conditions must be true to be counted.

    Quote Originally Posted by AlphaFrog View Post
    Try this for January.

    =SUMPRODUCT((MONTH(Sheet1!$D$2:$D$7)=1)*(Sheet1!$E$2:$E$7<=30))

    Thank you very much. It worked beautifully.

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
  •