sumproduct problems

m_woychick

New Member
Joined
Mar 22, 2004
Messages
33
I am trying to calculate the number of days it takes for an item to get from point a to point b. I get two reports--one that shows the object name and "point a" date and a second report that shows object name and "point b" date. All the object names in the first report may or may not be in the second report.

I put the report data into 4 columns:

object name a, point a, object name b, point b

My formula looks to see if object name a is the object name b column, subtract object name a's point a time from object name b's point b time.

using sumproduct, the formula looks like this

=SUMPRODUCT((A33=$C$32:$C$1032)*-(B33-$D$32:$D$1032))

I am getting some odd results, however. Here are some actual examples. The first looks OK, but the second should be ~17 days.

11/11/2003 0:07-11/3/2003 6:46=7.722824074
11/24/2003 8:17-11/7/2003 2:52=59.18696759

My guess is that I'm using sumproduct incorrectly or that I should be using some other function altogether. What I want to be able to do is see if the same object name exists in another column, and if so, subtract one number from another in different columns.

Any advice/insight would be great.
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
are you saying you want the difference only if they are on the same row? or find and match results, then subtract?
 

m_woychick

New Member
Joined
Mar 22, 2004
Messages
33
sorry I wasn't clear on these questions--

- the formula would be in column e, next to the point b date

- I would want to subtract the point a value from the point b value no matter where point b appears in that column. So I am looking to find match results, then subtract.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

something like this??...
Book3.xls
ABCDEF
4NameADateANameBDateBName:Days
5Alpha1/1/048:00Charlie1/21/0414:00Delta9.125
6Bravo1/2/048:00Delta1/13/0411:00
7Charlie1/3/048:00Bravo1/10/042:00
8Delta1/4/048:00Alpha1/18/0417:00
Sheet3


edit: change of format, sorry (added time)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
m_woychick said:
sorry I wasn't clear on these questions--

- the formula would be in column e, next to the point b date

- I would want to subtract the point a value from the point b value no matter where point b appears in that column. So I am looking to find match results, then subtract.

Column E is not a specific cell...

In F33 enter:

=MATCH(A33,$C$32:$C$1032,0)

In E33 enter:

=IF(ISNA(F33),"",INDEX($D$32:$D$1032,F33)-B33)
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

is notice aladin came up with a shorter answer to avoiding an error, but i'll post an option which can be swapped with my above posted scenario...

=IF(OR(ISNA(MATCH(E5,A5:A8,0)),ISNA(MATCH(E5,C5:C8,0))),"No Match",VLOOKUP(E5,C5:D8,2,0)-VLOOKUP(E5,A5:B8,2,0))
 

m_woychick

New Member
Joined
Mar 22, 2004
Messages
33
I was able to get the vlookup formula to work--thank you very much!

I have not been able to get the match formula to work yet, but I will play around with it some more.

Thanks again to all for your assistance!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
m_woychick said:
...
I have not been able to get the match formula to work yet, but I will play around with it some more...

It's the most efficient way of looking up things.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
i think what aladin was trying to show you, was something like this...
Book3.xls
ABCDEFGH
1NameADateANameBDateBAladin'ssolutionAnalternative
2Alpha1/1/048:00Charlie1/21/0414:0017.3754Alpha17.375
3Bravo1/2/048:00Delta1/13/0411:007.753
4Charlie1/3/048:00Bravo1/10/042:0018.251
5Delta1/4/048:00Alpha1/18/0417:009.1252
Sheet3


(hope i got that right :wink: )
 

Watch MrExcel Video

Forum statistics

Threads
1,122,190
Messages
5,594,761
Members
413,931
Latest member
acrato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top