Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Unanswered: CUMULATIVE COUNTIF...sorta

    I want to have a cumulative COUNTIFS. I want it to countif "criteria 1" is there and then add another one to the count if "criteria 2" is present and add another one to the count if criteria 4 is present so in the end I can get a total of 3 in the cell and not one.

    Now lets say I have a category column titled Teachers and I wanted to count how many teachers where in that cell.

    So CEll A3 = Language Arts Teacher
    Geography Teacher
    Math Teacher
    my count ifs would be =COUNTIFS(A2:A4,"*" & "Language Arts Teacher" & "*",J2:J4,"School A") + =COUNTIFS(A2:Af4,"*" & "Math Teacher" & "*",J2:J4,"School B") + ....etc

    What formula do I great so it can scan the cell and count the 3 teachers total ?

  2. #2
    Join Date
    Aug 2009
    Posts
    2
    =SUMPRODUCT(--(ISNUMBER(SEARCH({"Language Arts Teacher","Science Teacher","Math Teacher"},AU3))))

    Let me clarify, I want it to count only if cell J2:J4 contains "MELROSE HIGH SCHOOL" otherwise keep at 0.

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    I don't have XL 2007 at work to check, but this should do it -

    =SUM(COUNTIFS(A2:A4,{"*Language Arts Teacher*","*Science Teacher*","*Math Teacher*"},J2:J4,"MELROSE HIGH SCHOOL"))


    Of course, these all have the word "Teacher" in common so if you want all teachers you might be able to reduce it to just "*Teacher*" ?

    Hope that helps...
    Last edited by Colin Legg; 12-30-09 at 08:28.

Posting Permissions

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