+ Reply to Thread
Results 1 to 17 of 17

Countif or Sumif with multiple criteria

  1. #1
    Registered User
    Join Date
    06-05-2006
    Location
    Philippines
    Posts
    22

    Countif or Sumif with multiple criteria

    Hi Guys,

    I know this is simple but I just can't manage to crack a formula for this one.

    I need to count all the rows that satisfies 2 criteria:
    1.) Column D must have "VVV"
    2.) Column I should be "XXX"

    I need the answer to be reflected in a single cell. In short can you combo a formula using Countif or Sumif?

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Try,

    Sumif+Sumif

    Countif+Countif
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Registered User
    Join Date
    06-05-2006
    Location
    Philippines
    Posts
    22
    Sir,

    I think it will add all of those with
    1.) Column D must have "VVV"
    or
    2.) Column I should be "XXX"

    i actually need the formula to count only if
    1.) Column D must have "VVV"
    and
    2.) Column I should be "XXX"
    are both satisfied.

    any ideas?

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Try this

    =COUNTIF(D1:D5,"VVV")+COUNTIF(I1:I5,"XXX")

    or do you mean you only want to count column D cells where the same row in column I ="XXX"

  5. #5
    Registered User
    Join Date
    06-05-2006
    Location
    Philippines
    Posts
    22
    it actually adds all of the cells which complies either 1 on the 2 criteria. I was hoping to count it only if it will comply to both.

    .................Column D.......Column I
    Dont Count....ABC.......................
    Dont Count.......................VVV....

    COUNT..........ABC..............VVV.... this is the formula that i need to figure out.

    I hope you can help me with this.

  6. #6
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    The way to use sumif or countif with multiple criteria is to use an array formula or multiple cell formulas.

    Let me explain the multiple cell formulas first so you will get the idea.

    1.) Column D must have "VVV"
    2.) Column I should be "XXX"
    In column B (or any blank column) put the formula:

    =(D1="VVV")*(I1="XXX")

    Then, fill down this column as far as you need to. You will see 0 if either condition is false and 1 only if both conditions are true.

    To get the count where both are true, sum the column in which you wrote the functions.

  7. #7
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203

    Continuing my previous posting

    Now, to get the job done without using multiple columns to do it, you would use something like this:

    {=(D1:D20="VVV")*(I1:I20="XXX")}

    You DO NOT type the {} yourself. You type the formula without the {}, then instead of pressing Enter when you finish, you press Ctrl+Shift+Enter. Excel understands this key combination as defining an array formula, and it enters the {} for you.

    Caveat: You CANNOT use an entire column for an array formula. So, you need to be selective. You CAN use a NAME in place of the cell addresses.

    Hope this helps.

  8. #8
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Quote Originally Posted by Eladamri
    Hi Guys,

    I know this is simple but I just can't manage to crack a formula for this one.

    I need to count all the rows that satisfies 2 criteria:
    1.) Column D must have "VVV"
    2.) Column I should be "XXX"

    I need the answer to be reflected in a single cell. In short can you combo a formula using Countif or Sumif?
    To count...

    =SUMPRODUCT(--($D$2:$D$100="VVV"),--($I$2:$I$100="XXX"))

    To sum...

    =SUMPRODUCT(--($D$2:$D$100="VVV"),--($I$2:$I$100="XXX"),$J$2:$J$100)

    ...where the corresponding values in Column J are summed.

    Hope this helps!

  9. #9
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    That is a better solution than mine.

  10. #10
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303
    I understand that the following code works for the above situation.

    =SUMPRODUCT((A1:A19="ABC")*(B1:B19="VVV"))

    where in A Column, ABC is to be checked and in the B Column, VV is to be checked...

    But just wndering that why the Following code is not working??? Can you please suggest what I am doing wrong here:

    {=SUM(IF(AND(A1:A19="ABC",B1:B19="VVV"),1,0))}

    Any thoughts?
    Last edited by vikas.bhandari; 12-22-2006 at 03:28 AM.

  11. #11
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    {=SUM(IF(AND(A1:A19="ABC",B1:B19="VVV"),1,0))}

    What this say's is, if all cells in A1:A19="ABC" and all cells in B1:B19="VVV" that equals 1, otherwise it's equal to 0, then SUM 1 or 0

  12. #12
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303
    Quote Originally Posted by oldchippy
    {=SUM(IF(AND(A1:A19="ABC",B1:B19="VVV"),1,0))}

    What this say's is, if all cells in A1:A19="ABC" and all cells in B1:B19="VVV" that equals 1, otherwise it's equal to 0, then SUM 1 or 0

    Awesome, thnx so much!!!! One more thing..if we don't include {}, then what will be the difference??

  13. #13
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    You will get an error if you do not enter it as an Array - Ctrl+Shift+Enter

    Take a look at this link for more explanation

    http://www.cpearson.com/excel/array.htm

  14. #14
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303
    I thnk that link is down...any other links???

  15. #15

  16. #16
    Forum Contributor vikas.bhandari's Avatar
    Join Date
    04-07-2006
    Location
    Delhi, India
    MS-Off Ver
    Office 2007 and 2010
    Posts
    303
    Cool, thnx for the info!!! I found them nice!!!

    I have put thm on my Blog too...I hope you wont mind!!!

    Can you please goto http://excelnoob.blogspot.com and verifies if thats ok to post ur links there???

  17. #17
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Yep - great blog - thanks for the mention fame at last!

+ 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