Software

Ask something
Discussion Locked

Using 'AND' Logic in a 'SUMIF' Statement

0 helpful
10 replies

by ipctrain

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
In EXCEL 2003, how can I incorporate 'AND' logic in a 'SUMIF' statement? If not, what other function can I use to sum a column of cells given TWO conditions. This is what I have and it does not work: =SUMIF(Salary!G9:G62,62008+AND(Salary!J9:J62,40102),Salary!Y9:Y62). I'm testing G9:G62 for value "62008" AND J9:J62 for value "40102" then sum Y9:Y62, matching these conditions. The return value is entered on another worksheet. Thanks.

10 replies

by g...

Your () are set wrong:
=SUMIF(((Salary!G9:G62,62008)AND(Salary!J9:J62,40102)),Salary!Y9:Y62)
I think....

by bschaettle

In Excel, AND is a worksheet function, not an operator, so your syntax isn't correct. Try this:

=SUMIF(AND((Salary!G9:G62,62008),
(Salary!J9:J62,40102)),Salary!Y9:Y62)

by chitosunday

or you can use sumproduct
=sumproduct(Salary!G9:G62=62008)*
(Salary!J9:J62=40102)*(Salary!Y9:Y62))

by ipctrain

GREAT. THIS WAS A GREAT HELP!!! Thanks so much; your the best, ChitoSunday from IpcTrain

by aidanmatt

Hi, as far as I know, if you need to have multiple-conditions-satisfied SUM, use Array formula in Excel. Here is my solution to your conditions:
=SUM(IF(Salary!G9:G62=62008,if(Salary!J9:J62=40102,Salary!Y9:Y62)))

Hope this was useful.

Very important - do NOT use Enter key after typing/pasting this formula instead use Control+Enter combinatinon (this would put curly braces on both the ends of the formula, but won't work if you put them manually).

This discussion is closed to new replies.