Trying to master the IF(AND) function but cannot seem to get the result I want

Example: If Cell F1:F326="2-Color" and Cell L1:L326="4" and Cell N1:N326="1"
then sum Cell M1:M326 that match the above criteria and place that value in
Sheet2 Cell A1.
So if F1 says "2-Color" and L1 says "4" and N1 says "1" and the value for M1
is 2700.
Also F4 says "2-Color" and L1 says "4" and N1 says "1" and the value for M4
is 1300.
Than Sheet 2 Cell A1 should read "4000".

I tried this formula with no success:
=IF(AND(Counts!F3:F326="2-Color",AND(Counts!L3:L326="4",AND(Counts!N3:N326=" 1"))),SUM(Counts!M3:M326),"ain't happenin")

any assistance is greatly appreciated

Try SUMPRODUCT() instead:

=SUMPRODUCT(--(Counts!F3:F326="2-Color"),--(Counts!L3:L326=4),
--(Counts!N3:N326=1),Counts!M3:M326)

for an explanation of "--" see

http://www.mcgimpsey.com/excel/doubleneg.html

To do it the way you were going to, array-enter (CTRL-SHIFT-ENTER or
CMD-RETURN):

=SUM(IF((Counts!F3:F326="2-Color")*(Counts!L3:L326=4)*
(Counts!N3:N326=1),Counts!M3:M326))


NOTE: I took the numbers out of parens assuming that you wanted to
compare numbers, rather than Text.

In article ,
"Murph" wrote:

Trying to master the IF(AND) function but cannot seem to get the result I
want

Example: If Cell F1:F326="2-Color" and Cell L1:L326="4" and Cell N1:N326="1"
then sum Cell M1:M326 that match the above criteria and place that value in
Sheet2 Cell A1.
So if F1 says "2-Color" and L1 says "4" and N1 says "1" and the value for M1
is 2700.
Also F4 says "2-Color" and L1 says "4" and N1 says "1" and the value for M4
is 1300.
Than Sheet 2 Cell A1 should read "4000".

I tried this formula with no success:
=IF(AND(Counts!F3:F326="2-Color",AND(Counts!L3:L326="4",AND(Counts!N3:N326=" 1"
))),SUM(Counts!M3:M326),"ain't happenin")

any assistance is greatly appreciated

Try this in SHEET2, cell A1:

=SUMPRODUCT(--(SHEET1!$F$1:$F$999="2-Color"),--(SHEET1!$L$1:$L$999="4"),--(SHEET1!$N$1:$N$999="1"),SHEET1!$M$9:$M$999)

-Tim U

"Murph" wrote:

Trying to master the IF(AND) function but cannot seem to get the result I want

Example: If Cell F1:F326="2-Color" and Cell L1:L326="4" and Cell N1:N326="1"
then sum Cell M1:M326 that match the above criteria and place that value in
Sheet2 Cell A1.
So if F1 says "2-Color" and L1 says "4" and N1 says "1" and the value for M1
is 2700.
Also F4 says "2-Color" and L1 says "4" and N1 says "1" and the value for M4
is 1300.
Than Sheet 2 Cell A1 should read "4000".

I tried this formula with no success:
=IF(AND(Counts!F3:F326="2-Color",AND(Counts!L3:L326="4",AND(Counts!N3:N326=" 1"))),SUM(Counts!M3:M326),"ain't happenin")

any assistance is greatly appreciated

=IF(AND(Counts!F3:F326="2-Color",Counts!L3:L326="4",Counts!N3:N326="1"),SUM( Counts!M3:M326),"ain't happenin")

You had too many 'AND's in there. Also, this is an array...
Enter the formula above BUT instead of hitting ENTER when you are done, hit
Ctrl-Shift-Enter.
HTH,
--
Gary Brown



"Murph" wrote:

Trying to master the IF(AND) function but cannot seem to get the result I want

Example: If Cell F1:F326="2-Color" and Cell L1:L326="4" and Cell N1:N326="1"
then sum Cell M1:M326 that match the above criteria and place that value in
Sheet2 Cell A1.
So if F1 says "2-Color" and L1 says "4" and N1 says "1" and the value for M1
is 2700.
Also F4 says "2-Color" and L1 says "4" and N1 says "1" and the value for M4
is 1300.
Than Sheet 2 Cell A1 should read "4000".

I tried this formula with no success:
=IF(AND(Counts!F3:F326="2-Color",AND(Counts!L3:L326="4",AND(Counts!N3:N326=" 1"))),SUM(Counts!M3:M326),"ain't happenin")

any assistance is greatly appreciated