Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 4th 06, 11:30 AM posted to microsoft.public.excel.worksheet.functions
1990
 
Posts: n/a
Default SUMIF with 2 conditions


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


  #2   Report Post  
Old January 4th 06, 12:09 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default SUMIF with 2 conditions

=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" wrote in message
...

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



  #3   Report Post  
Old January 4th 06, 12:52 PM posted to microsoft.public.excel.worksheet.functions
1990
 
Posts: n/a
Default SUMIF with 2 conditions


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

  #4   Report Post  
Old January 4th 06, 01:08 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default SUMIF with 2 conditions

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" wrote in message
...

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



  #5   Report Post  
Old January 4th 06, 01:34 PM posted to microsoft.public.excel.worksheet.functions
Vito
 
Posts: n/a
Default SUMIF with 2 conditions


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" wrote in
message
...

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



  #6   Report Post  
Old January 4th 06, 01:37 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default SUMIF with 2 conditions

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Vito" wrote in message
...

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" wrote in
message
...

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



  #7   Report Post  
Old January 4th 06, 02:33 PM posted to microsoft.public.excel.worksheet.functions
1990
 
Posts: n/a
Default SUMIF with 2 conditions


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

  #8   Report Post  
Old January 4th 06, 05:57 PM posted to microsoft.public.excel.worksheet.functions
ellebelle
 
Posts: n/a
Default SUMIF with 2 conditions

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


  #9   Report Post  
Old January 4th 06, 10:39 PM posted to microsoft.public.excel.worksheet.functions
Kleev
 
Posts: n/a
Default SUMIF with 2 conditions

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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMIF function with 2 conditions rlandlin Excel Worksheet Functions 4 September 28th 05 05:50 PM
sumif with 2 conditions ?? can this be done?? WTG Excel Worksheet Functions 2 July 4th 05 10:08 PM
SUMIF with two conditions Fred Holmes Excel Discussion (Misc queries) 3 May 1st 05 10:10 PM
sumif with two conditions ww Excel Worksheet Functions 3 March 31st 05 01:44 AM
SUMIF based on 2 conditions TimH Excel Worksheet Functions 3 October 28th 04 08:18 PM


All times are GMT +1. The time now is 12:18 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017