SUMIF with 2 conditions

[1990]

Can anyone help, I have a spreadsheet with 2 sheets, the first sheet contains the data, the second sheet is a summary page.

Sheet 1 contains 8 colums (a - h), colum f contains price information, colum g contains the department number and colum h contains the product code.

What I would like to do on the summary sheet is have a cell where you can input a deprtment number and then a list of all the product codes with a formula to work out the price of each of the products for that deprtment number.

I can work out, with " =SUMIF('Stock Issues'!H:H,A2,'Stock Issues'!F:F) " the totals for each product code, but is it possible to add the department condition?

[Bob Phillips]

=SUMPRODUCT(--('Stock Issues'!G2:G200=$A$1),--('Stock
Issues'H2:H200=B1),'Stock Issues'F2:F200)

and copy down, where A1 has the department number, B1:Bn has the product
codes. Note that SUMPRODUCT cannot use whole columns, only a specified
range.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"1990" <1990.213yka_1136374501.4504@excelforum-nospam.com> wrote in message
news:1990.213yka_1136374501.4504@excelforum-nospam.com...
>
> Can anyone help, I have a spreadsheet with 2 sheets, the first sheet
> contains the data, the second sheet is a summary page.
>
> Sheet 1 contains 8 colums (a - h), colum f contains price information,
> colum g contains the department number and colum h contains the product
> code.
>
> What I would like to do on the summary sheet is have a cell where you
> can input a deprtment number and then a list of all the product codes
> with a formula to work out the price of each of the products for that
> deprtment number.
>
> I can work out, with " =SUMIF('Stock Issues'!H:H,A2,'Stock
> Issues'!F:F) " the totals for each product code, but is it possible to
> add the department condition?
>
>
> --
> 1990
> ------------------------------------------------------------------------
> 1990's Profile:
http://www.excelforum.com/member.php...o&userid=30100
> View this thread: http://www.excelforum.com/showthread...hreadid=497851
>

[1990]

Thanks for the reply, but when i copy that in it brings up an error message and highlights the second 'Stock - highlighted in red here " =SUMPRODUCT(--('Stock Issues'!G2:G200=B1),--('Stock Issues'H2:H200=$A$1),'Stock Issues'F2:F200) "

[Bob Phillips]

Maybe wrap-around.

Try copying

=SUMPRODUCT(--('Stock Issues'!G2:G200=$A$1),
--('Stock Issues'H2:H200=B1),'Stock Issues'F2:F200)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"1990" <1990.21429n_1136379303.0197@excelforum-nospam.com> wrote in message
news:1990.21429n_1136379303.0197@excelforum-nospam.com...
>
> Thanks for the reply, but when i copy that in it brings up an error
> message and highlights the second 'Stock - highlighted in red here "
> =SUMPRODUCT(--('Stock Issues'!G2:G200=B1),--('Stock
> Issues'H2:H200=$A$1),'Stock Issues'F2:F200) "
>
>
> --
> 1990
> ------------------------------------------------------------------------
> 1990's Profile:
http://www.excelforum.com/member.php...o&userid=30100
> View this thread: http://www.excelforum.com/showthread...hreadid=497851
>

[Vito]

Maybe wrap-around.

Try copying

=SUMPRODUCT(--('Stock Issues'!G2:G200=$A$1),
--('Stock Issues'H2:H200=B1),'Stock Issues'F2:F200)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"1990" <1990.21429n_1136379303.0197@excelforum-nospam.com> wrote in message
news:1990.21429n_1136379303.0197@excelforum-nospam.com...
>
> Thanks for the reply, but when i copy that in it brings up an error
> message and highlights the second 'Stock - highlighted in red here "
> =SUMPRODUCT(--('Stock Issues'!G2:G200=B1),--('Stock
> Issues'H2:H200=$A$1),'Stock Issues'F2:F200) "
>
>
> --
> 1990
> ------------------------------------------------------------------------
> 1990's Profile:
http://www.excelforum.com/member.php...o&userid=30100
> View this thread: http://www.excelforum.com/showthread...hreadid=497851
>

The exclamation points are necessary after each reference to a worksheet.

=SUMPRODUCT(--('Stock Issues'!G2:G200=$A$1),--('Stock Issues'!H2:H200=B1),'Stock Issues'!F2:F200)

[Bob Phillips]

Oops, didn't even notice I had dropped those in the copy.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Vito" <Vito.21444a_1136381702.8416@excelforum-nospam.com> wrote in message
news:Vito.21444a_1136381702.8416@excelforum-nospam.com...
>
> Bob Phillips Wrote:
> > Maybe wrap-around.
> >
> > Try copying
> >
> > =SUMPRODUCT(--('Stock Issues'!G2:G200=$A$1),
> > --('Stock Issues'H2:H200=B1),'Stock Issues'F2:F200)
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "1990" <1990.21429n_1136379303.0197@excelforum-nospam.com> wrote in
> > message
> > news:1990.21429n_1136379303.0197@excelforum-nospam.com...
> > >
> > > Thanks for the reply, but when i copy that in it brings up an error
> > > message and highlights the second 'Stock - highlighted in red here
> > "
> > > =SUMPRODUCT(--('Stock Issues'!G2:G200=B1),--('Stock
> > > Issues'H2:H200=$A$1),'Stock Issues'F2:F200) "
> > >
> > >
> > > --
> > > 1990
> > >
> > ------------------------------------------------------------------------
> > > 1990's Profile:
> > http://www.excelforum.com/member.php...o&userid=30100
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=497851
> > >
>
>
> The exclamation points are necessary after *each* reference to a
> worksheet.
>
> =SUMPRODUCT(--('Stock Issues'!G2:G200=$A$1),--('Stock
> Issues'!H2:H200=B1),'Stock Issues'!F2:F200)
>
>
> --
> Vito
> ------------------------------------------------------------------------
> Vito's Profile:
http://www.excelforum.com/member.php...o&userid=29182
> View this thread: http://www.excelforum.com/showthread...hreadid=497851
>

[1990]

Spot on, thanks.

[ellebelle]

what does the symbol below mean in your formula above
--

i don't have it?

"1990" wrote:

>
> Spot on, thanks.
>
>
> --
> 1990
> ------------------------------------------------------------------------
> 1990's Profile: http://www.excelforum.com/member.php...o&userid=30100
> View this thread: http://www.excelforum.com/showthread...hreadid=497851
>
>

[Kleev]

That's 2 minus signs (double unary operator.) They are used to coerce
logical true and false values to 1s and 0s. Let me see if I can find the
link to Bob's site which explains sumproduct very well:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

"ellebelle" wrote:

> what does the symbol below mean in your formula above
> --
>
> i don't have it?
>
> "1990" wrote:
>
> >
> > Spot on, thanks.
> >
> >
> > --
> > 1990
> > ------------------------------------------------------------------------
> > 1990's Profile: http://www.excelforum.com/member.php...o&userid=30100
> > View this thread: http://www.excelforum.com/showthread...hreadid=497851
> >
> >