+ Reply to Thread
Results 1 to 7 of 7

CountIFs with multiple criteria

Hybrid View

  1. #1
    Registered User
    Join Date
    05-04-2011
    Location
    Washington, DC
    MS-Off Ver
    Excel 2007
    Posts
    4

    CountIFs with multiple criteria

    Hi all,

    I have a listed of donations that I need to count for a given month and year.
    Which is all well and fine, except that I have do it for a particular segment as well (eg $50-75, $75-100, etc) This means there needs to be more than one criteria for my CountIF formula- greater than a value AND less than a value. I've looked through a series of other suggestions on the internet and tried to use the difference of two CountIFs to make this possible.
    However, when I do this
    =(COUNTIFS($F$2:$F$557898,"November",$G$2:$G$557898,"2008",$D$2:$D$557898,">75")-COUNTIFS($F$2:$F$557898,"November",$G$2:$G$557898,"2008",$D$2:$D$557898,"<=100"))
    I get a negative value for what I am trying to count. I know this can't be right.
    (eg: $ -1,481)

    Where am I going wrong here? Can anyone please share any thoughts?
    Attached Images Attached Images

  2. #2
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    365
    Posts
    1,256

    Re: CountIFs with multiple criteria

    Since you take away one from the other, the criteria in the second part should be ,">100"
    =(COUNTIFS($F$2:$F$557898,"November",$G$2:$G$557898,"2008",$D$2:$D$557898,">75")
       -COUNTIFS($F$2:$F$557898,"November",$G$2:$G$557898,"2008",$D$2:$D$557898,">100"))

    But you can simplify it into a single COUNTIFS function:
    =COUNTIFS($F$2:$F$557898,"November",$G$2:$G$557898,"2008",$D$2:$D$557898,">75",$D$2:$D$557898,"<=100")
    *untested
    Last edited by Colin Legg; 05-04-2011 at 01:17 PM.
    Hope that helps,

    Colin

    RAD Excel Blog

  3. #3
    Registered User
    Join Date
    05-04-2011
    Location
    Washington, DC
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: CountIFs with multiple criteria

    Thank you!
    You have been most helpful.
    Would it be possible to do the same thing with a SUMIF function, as well?

  4. #4
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    365
    Posts
    1,256

    Re: CountIFs with multiple criteria

    Do you mean SUMIFS()?

    Yes, same principals apply to COUNTIF/SUMIF/COUNTIFS/SUMIFS.

  5. #5
    Registered User
    Join Date
    05-04-2011
    Location
    Washington, DC
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: CountIFs with multiple criteria

    Yes.
    Would the following be a correct way of applying the same principle to a SUMIFS() function?
    =SUMIFS($D$2:$D$557898,$F$2:$F$557898,"November",$G$2:$G$557898,"2008",$D$2:$D$557898,">75",$D$2:$D$557898,"<=100")

  6. #6
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    365
    Posts
    1,256

    Re: CountIFs with multiple criteria

    Looks good... did you give it a try?

  7. #7
    Registered User
    Join Date
    05-04-2011
    Location
    Washington, DC
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: CountIFs with multiple criteria

    Hurrah! It worked.
    You just saved me two days of hair-pulling.
    Muchos Gracias!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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