General discussion

Locked

Using 'AND' Logic in a 'SUMIF' Statement

By IPCTrain ·
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.

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by G... In reply to Using 'AND' Logic in a 'S ...

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

Collapse -

by IPCTrain In reply to

Poster rated this answer.

Collapse -

by bschaettle In reply to Using 'AND' Logic in a 'S ...

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)

Collapse -

by bschaettle In reply to

....or maybe use the MATCH function to look for 62008 and 40102.

Collapse -

by IPCTrain In reply to

Poster rated this answer.

Collapse -

by chitosunday In reply to Using 'AND' Logic in a 'S ...

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

Collapse -

by IPCTrain In reply to

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

Collapse -

by AidanMatt In reply to Using 'AND' Logic in a 'S ...

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).

Collapse -

by IPCTrain In reply to

Poster rated this answer.

Collapse -

by IPCTrain In reply to Using 'AND' Logic in a 'S ...

This question was closed by the author

Back to Software Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums