sum if and sum if

[lleytte]

I'd like to sum if two conditions are met. Here's what I've tried, but didn't
work.
=SUM(IF(E1275:E1299,A16,AN1275:AN1299)*(G1275:G1299,A14,AN1275:AN1299))

I also tried
=SUMIF(E1275:E1299,A16,AN1275:AN1299)*(G1275:G1299,A14,AN1275:AN1299)

My final attempt
=sum(if(E1275:E1299=A16)*(G1275:G1299=A14),AN1275:AN1299,false)

all I get is #VALUE? or #NAME? or it doesn't accept

Can someone tell me what I'm doing wrong? Thanks.

 

[Pete_UK]

Slight amendment to your final attempt:

=SUM(IF((E1275:E1299=A16)*(G1275:G1299=A14),AN1275:AN1299))

then commit using Ctrl-Shift-Enter instead of the usual <Enter>,
because this is an array formula.

Hope this helps.

Pete

 

[lleytte]

I'm actually using it between sheets in a workbook so I input
=sum(if(('Jul ''08'!E1275:E1299='SPC Report'!A16)*('Jul
''08'!G1275:G1299='SPC Report'!'SPC Report'!A14),'Jul ''08'!AX1275:AX1299))

then commit, but it doesn't accept the formula.

Does it not work this way?

 

[Pete_UK]

Try it like this:

=sum(if(('Jul 08'!E1275:E1299='SPC Report'!A16)*('Jul 08'!
G1275:G1299='SPC Report'!'SPC Report'!A14),'Jul 08'!AX1275:AX1299))

You had two apostrophes between your sheet name for Jul 08. Use CSE to
commit.

Hope this helps.

Pete

 

[Max]

Think you had some typos/extra bits in your expression

I managed to get Excel to accept this cleaned up expression,
array-entered (press CTRL+SHIFT+ENTER):
=SUM(IF(('Jul ''08'!E1275:E1299='SPC Report'!A16)*('Jul
''08'!G1275:G1299='SPC Report'!A14),'Jul ''08'!AX1275:AX1299))

It assumes of course, that you have 2 other sheets named:
SPC Report
Jul '08
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,400 Files:356 Subscribers:53
xdemechanik