Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can there be multiple sumifs or countifs? (Excel 2000)

    Hi,

    I have a spreadsheet that I am using as a shareable workbook across the organization. Therefore Pivot reports are not accessible. Its like this: I have one column on Project Code, one on Broad skill (Microsoft, Java, Multiple), one on status (Billable, Buffer, Trainee). I want to count the total number of say, billable resources in ABC project who are into Microsoft. The conditional Sum wizard (Tools->Wizard->Conditional Sum) can only sum a column with preset conditions. But all my columns are text columns. Pivot could easily do a count , but conditional sum can't. What is the best alternative? Is there a way I could use AND, COUNTIF, SUMIF, etc together or is there an existing formula to take care of my needs?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can there be multiple sumifs or countifs? (Excel 2000)

    Say that Project Codes are in A2:A37 and Broad Skills in B2:B37. To count the number of entries with "ABC" in column A and "Microsoft" in column B, use the following formula:

    =SUM((A2:A37="ABC")*(B2:B37="Microsoft"))

    This is an array formula, i.e. it must be confirmed with Ctrl+Shift+Enter instead of just Enter. You can use named ranges instead of A2:A37 and B2:B37. The formula can be expanded to include criteria on more columns if necessary.

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can there be multiple sumifs or countifs? (Excel 2000)

    Thanks a lot, Hans!

  4. #4
    New Lounger
    Join Date
    Feb 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can there be multiple sumifs or countifs? (Excel 2000)

    Hans..It is showing the #Num! error. Any clue?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can there be multiple sumifs or countifs? (Excel 2000)

    Check carefully that you have used the correct criteria. String values such as "Microsoft" should be enclosed in quotes, numeric values shouldn't. Also note the placement of parentheses. Finally, after each modification, you must confirm the formula with Ctrl+Shift+Enter.

    I have attached my demo workbook. If you still have problems, you might consider posting your workbook, or a stripped down version. Be sure to erase or modify sensitive information.

Posting Permissions

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