Ask Experts Questions for FREE Help !
Ask
    gilda102's Avatar
    gilda102 Posts: 2, Reputation: 1
    New Member
    		  
    #1

    Aug 1, 2008, 08:34 AM
    SUMIF with 2 criteria
    Hello All,
    I'm relatively new to using functions in excel and I'm sure my problem is really very simple but I am having a hard time figuring it out. I want to use the SUMIF function to total column C but only if column A=4012 and if column B=UK.

    A B C
    G/L Code Solic. Gift
    4011 PE $100.00
    4010 UK $75.00
    4012 UK $200.00

    I keep getting a #VALUE error when I try to add the second criteria.
    Thanks for any help on this.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #2

    Aug 1, 2008, 10:26 AM
    Code:
    =SUMPRODUCT((A2:A100=4012)*(B2:B100="UK")*(C2:C100))
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #3

    Aug 1, 2008, 10:31 AM
    If the list is a growing list, you can NAME the data fields to make your formula easier to understand and self-adjust:

    Highlight all the A column EXCEPT the title cell, then type PLCode in the Name box just above the A row. Repeat for the B row, except name it Solic. Name the C row Gift. Remember to NOT include the title row.

    Now the formula can be changed to this and it reads like a sentence:
    Code:
    =SUMPRODUCT((PLCode=4012)*(Solic="UK")*(Gift))
    Attached Images
     
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Criteria of Comparable worth. [ 2 Answers ]

Is criteria of comparable worth an appropriate form of remedy?

Criteria for becoming bonded [ 4 Answers ]

I was recently asked on an application if I was able to be bonded. I wanted to know What types of offenses would stop a person from becoming bonded? Do you have to be convicted of the crime and does traffic violations count?

Criteria for being bondable [ 2 Answers ]

My 18-year-old made a very stupid decision when she took 2 cheques from her room mate that were already endorsed by him and filled in with an amount, but the pay to was blank. She filled in her name and deposited them in her bank account to get back at him for being so nasty to her. She clearly...

2 criteria to count and can't run queries [ 1 Answers ]

I am on a Corporate networked computer and it won't allow queries or macros. I need to count values in one column only if they match with values in another column. Is there no formula (i.e. countif) that can do this for me?

Counting when there are 2 criteria [ 1 Answers ]

In a spreadsheet, how do I get a count total if there are two criteria? For example, in one column I want to count all the "1"'s if, in another column there is a "c".

View more questions Search