+ Reply to Thread
Results 1 to 4 of 4

CountIfs Formula Question - adding a second criteria

  1. #1
    Registered User
    Join Date
    01-04-2012
    Location
    Minneapolis, MN
    MS-Off Ver
    Excel 2007
    Posts
    5

    CountIfs Formula Question - adding a second criteria

    I'm working on a CountIfs formula for a very large spreadsheet. Right now the CountIfs formula limits to persons receiving both Item 1 and Item 2.

    =IF(COUNTIFS($A$2:$A$62,A2,$H$2:$H$62,"Item 1")*COUNTIFS($A$2:$A$62,A2,$H$2:$H$62,"Item 2")=0,"",1)

    However, I'd like to flag only data where in one month a person received both item 1 and Item 2. How could expand my CountIfs formula to limit by month/Column C on my example?

    Thank you!
    Christine
    Attached Files Attached Files

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: CountIfs Formula Question - adding a second criteria

    try...in Row 2(Untested)

    Please Login or Register  to view this content.
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: CountIfs Formula Question - adding a second criteria

    I use pivot table for that.

    See for an example the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,579

    Re: CountIfs Formula Question - adding a second criteria

    I used

    =SUMPRODUCT(--($H$2:$H$62="Item 1")+($H$2:$H$62="Item 2"), --(MONTH($C$2:$C$62)=MONTH(C2)),--($E$2:$E$62=E2))
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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