# =SUMIF

1. ## =SUMIF

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
--
He4Giv (****)

2. ## Re: =SUMIF

Try SUMPRODUCT:

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

HTH
Jason
Atlanta, GA

>-----Original Message-----
>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
>--
>He4Giv (****)
>.
>

3. ## Re: =SUMIF

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?

"Jason Morin" wrote:

> Try SUMPRODUCT:
>
> =SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)*G2:G15)
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >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
> >--
> >He4Giv (****)
> >.
> >

>

4. ## RE: =SUMIF

=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

"He4Giv" wrote:

> 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
> --
> He4Giv (****)

5. ## Re: =SUMIF

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

>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?
>
>"Jason Morin" wrote:
>
>> Try SUMPRODUCT:
>>
>> =SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)*G2:G15)
>>
>> HTH
>> Jason
>> Atlanta, GA
>>
>> >-----Original Message-----
>> >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
>> >--
>> >He4Giv (****)
>> >.
>> >

>>

>.
>

6. ## Re: =SUMIF

try
BARLSIT LOG
' BARLSIT LOG'

--
Don Guillett
SalesAid Software
donaldb@281.com
"He4Giv" <He4Giv@discussions.microsoft.com> wrote in 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?
>
> "Jason Morin" wrote:
>
> > Try SUMPRODUCT:
> >
> > =SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)*G2:G15)
> >
> > HTH
> > Jason
> > Atlanta, GA
> >
> > >-----Original Message-----
> > >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
> > >--
> > >He4Giv (****)
> > >.
> > >

> >

7. ## Re: =SUMIF

"He4Giv" <He4Giv@discussions.microsoft.com> wrote
> 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.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

8. ## Re: =SUMIF

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"

"Jason Morin" wrote:

> 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?
> >
> >"Jason Morin" wrote:
> >
> >> Try SUMPRODUCT:
> >>
> >> =SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)*G2:G15)
> >>
> >> HTH
> >> Jason
> >> Atlanta, GA
> >>
> >> >-----Original Message-----
> >> >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
> >> >--
> >> >He4Giv (****)
> >> >.
> >> >
> >>

> >.
> >

>

9. ## Re: =SUMIF

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"He4Giv" <He4Giv@discussions.microsoft.com> wrote in message
news:8519134F-3443-4274-8296-91E465C28D0A@microsoft.com...
> 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?

10. ## Re: =SUMIF

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

>-----Original Message-----
>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"
>
>"Jason Morin" wrote:
>
>> 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?
>> >
>> >"Jason Morin" wrote:
>> >
>> >> Try SUMPRODUCT:
>> >>
>> >> =SUMPRODUCT((Sheet2!H2:H15=1)*(Sheet2!J2:J15=9)

*G2:G15)
>> >>
>> >> HTH
>> >> Jason
>> >> Atlanta, GA
>> >>
>> >> >-----Original Message-----
>> >> >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
>> >> >--
>> >> >He4Giv (****)
>> >> >.
>> >> >
>> >>
>> >.
>> >

>>

>.
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1