SUMIF with 2 conditions

[1990]

Can anyone help, I have a spreadsheet with 2 sheets, the first shee
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 produc
code.

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

I can work out, with " =SUMIF('Stock Issues'!H:H,A2,'Stoc
Issues'!F:F) " the totals for each product code, but is it possible t
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]

Thanks for the reply, but when i copy that in it brings up an erro
message and highlights the second 'Stock - highlighted in red here
=SUMPRODUCT(--('Stock Issues'!G2:G200=B1),--('Stoc
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)

 

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

message

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)

 

[1990]

Spot on, thanks

 

[Guest]

what does the symbol below mean in your formula above

 

[Guest]

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