=SUMIF

[Guest]

Hello
I want to put my formula on sheet1 and reference columns on sheet2 for example
=sumif(Sheet2!H2:H15=1,and if sheet2!J2:J15=9,sheet2!g2:g15))
Basically I want to have the sumif check column H for the #1 AND aslo check
Column J for the #9 and if it finds both numbers in any one row to grab the
figure from the G column and put it on Sheet1.
I tried different variations and I get either #REF or #NAME and sometimes
the "File not found" window pops up when I try to place the formula.
I know how to write a one senaro SUMIF but I get confused when I want it to
check two columns and if it finds the #1 and the #9 in two columns to carry
out the SUMIF.
Thank you

 

[Jason Morin]

Try SUMPRODUCT:

=SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)*G2:G15)

HTH
Jason
Atlanta, GA

 

[Guest]

I pated your formula into the cell and edited Sheet2 to read the name on my
tab which is BARLSIT LOG. When I did so the "File not found" window came up
like its looking for a file and in the cell it pasted #NAME?

Try SUMPRODUCT:

=SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)*G2:G15)

HTH
Jason
Atlanta, GA

 

[Guest]

=SUM(IF(Sheet3!$A$29:$A$34=1,IF(Sheet3!$C$29:$C$34=9,Sheet3!$B$29:$B$34,0),0))
this formula was on sheet one, the table was on sheet three:

one two three

1 12 9


1 14
15 9
The formula returned 12.

I used an Excel wizard called the 'conditional sum wizard'. yoiu have to go
to Tools|Addins to see if you have it to install. If it is in the list of
available addins, check the box and OK.

Bear

 

[Jason Morin]

You probably left out the apostrophes that surround the
sheet name (Excel includes them when there is a space in
the sheet name). Rather than editing my formula, re-
create it and click on the ranges - Excel will fill in
the sheet name properly.

Jason

-----Original Message-----
I pated your formula into the cell and edited Sheet2 to read the name on my
tab which is BARLSIT LOG. When I did so the "File not found" window came up
like its looking for a file and in the cell it pasted #NAME?

 

[Don Guillett]

try
BARLSIT LOG
' BARLSIT LOG'

--
Don Guillett
SalesAid Software
(e-mail address removed)

I pated your formula into the cell and edited Sheet2 to read the name on my
tab which is BARLSIT LOG. When I did so the "File not found" window came up
like its looking for a file and in the cell it pasted #NAME?

 

[Max]

I pated your formula into the cell and edited Sheet2 to read the name on my
tab which is BARLSIT LOG. When I did so the "File not found" window came up
like its looking for a file and in the cell it pasted #NAME?

Think it's looking for "Sheet2",
which presumably doesn't exist in your book.

Its usually easier to rename your sheetname to suit the formula suggested
first, e.g.: rename "BARLSIT LOG" to "Sheet2", then paste-in the formula and
get it working fine.

And then you could rename the sheet back to its original sheetname, and
leave it to Excel to auto-adjust the formula, which would happen in this
case.

 

[Guest]

the sumproduct worked great when i added the tick marks each end, ie:'BARLIST
LOG' and I forgot that I needed to hit CTRL+SHIFT+ENTER since its an array
formula.
question: If I wanted to add a 3rd senaro, or thrid column How would you
write it?
The thrid condition would be 'Barlist Log'!I2:I15,"BLK"

 

[Max]

... I needed to hit CTRL+SHIFT+ENTER

since its an array formula.

For SUMPRODUCT?
Think just a normal press ENTER should do it

The thrid condition would be 'Barlist Log'!I2:I15,"BLK"

Just "add-on" the criteria in the formula, like :

=SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)*(Sheet2!I2:I15="BLK")*G2:G15
)

 

[Biff]

Hi!

=SUMPRODUCT(('Barlist Log'!H2:H15=1)*('Barlist Log'!
I2:I15="blk")*('Barlist Log'!J2:J15=9)*G2:G15)

No need to enter as an array formula. The SUMPRODUCT
function accepts arrays as arguments.

Biff