Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 27th 04, 07:36 PM
Pamela
 
Posts: n/a
Default index match and sum

Hello All,
I have a listing of product orders in a workbook. In another workbook I
want to find the product, and sum the quantity of orders I have for each
product. I have the index/match down, but it only returns the first value,
I need it to go through the range and sum all the values.
Here's an example of what I have, any help or hints would be appreciated.

=if(iserror(match($a2,Orders!J:J,0)),"0",index(Ord ers!I:I,match($a2,Orders!J
:J,0)))
Thanks
Pam




  #2   Report Post  
Old October 27th 04, 07:49 PM
Myrna Larson
 
Posts: n/a
Default

Use COUNTIF. It's documented in Help. If you need the total value of those
orders and you have a column with the order total, it would be SUMIF. Again,
see Help.


On Wed, 27 Oct 2004 14:36:58 -0400, "Pamela"
wrote:

Hello All,
I have a listing of product orders in a workbook. In another workbook I
want to find the product, and sum the quantity of orders I have for each
product. I have the index/match down, but it only returns the first value,
I need it to go through the range and sum all the values.
Here's an example of what I have, any help or hints would be appreciated.

=if(iserror(match($a2,Orders!J:J,0)),"0",index(Or ders!I:I,match($a2,Orders!J
:J,0)))
Thanks
Pam



  #3   Report Post  
Old October 27th 04, 07:51 PM
Jason Morin
 
Posts: n/a
Default

=SUMIF(Orders!J:J,$A2,Orders!I:I)

HTH
Jason
Atlanta, GA

-----Original Message-----
Hello All,
I have a listing of product orders in a workbook. In

another workbook I
want to find the product, and sum the quantity of orders

I have for each
product. I have the index/match down, but it only

returns the first value,
I need it to go through the range and sum all the values.
Here's an example of what I have, any help or hints

would be appreciated.

=if(iserror(match($a2,Orders!J:J,0)),"0",index(Or ders!

I:I,match($a2,Orders!J
:J,0)))
Thanks
Pam



.

  #4   Report Post  
Old October 27th 04, 07:59 PM
Pamela
 
Posts: n/a
Default

Thanks,
That works!
Pam
"Myrna Larson" wrote in message
...
Use COUNTIF. It's documented in Help. If you need the total value of those
orders and you have a column with the order total, it would be SUMIF.

Again,
see Help.


On Wed, 27 Oct 2004 14:36:58 -0400, "Pamela"
wrote:

Hello All,
I have a listing of product orders in a workbook. In another workbook I
want to find the product, and sum the quantity of orders I have for each
product. I have the index/match down, but it only returns the first

value,
I need it to go through the range and sum all the values.
Here's an example of what I have, any help or hints would be appreciated.


=if(iserror(match($a2,Orders!J:J,0)),"0",index(Or ders!I:I,match($a2,Orders!

J
:J,0)))
Thanks
Pam





  #5   Report Post  
Old October 27th 04, 08:10 PM
Pamela
 
Posts: n/a
Default

I take that back, The SUMIF works, but it brings over the wrong totals, I
have retyped the partnumbers so I know it is matching correctly, but it is
dropping some and adding some other places, could you look at this formula
and tell me what I could have done wrong?
=SUMIF(Orders!$I:$I,MATCH($a3,Orders!$J:$J,0))
Thanks
Pam
"Myrna Larson"


wrote in message
...
Use COUNTIF. It's documented in Help. If you need the total value of those
orders and you have a column with the order total, it would be SUMIF.

Again,
see Help.


On Wed, 27 Oct 2004 14:36:58 -0400, "Pamela"
wrote:

Hello All,
I have a listing of product orders in a workbook. In another workbook I
want to find the product, and sum the quantity of orders I have for each
product. I have the index/match down, but it only returns the first

value,
I need it to go through the range and sum all the values.
Here's an example of what I have, any help or hints would be appreciated.


=if(iserror(match($a2,Orders!J:J,0)),"0",index(Or ders!I:I,match($a2,Orders!

J
:J,0)))
Thanks
Pam







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



All times are GMT +1. The time now is 12:49 PM.

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