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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
are you saying you want the difference only if they are on the same row? or find and match results, then subtract?
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
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))
 
Upvote 0
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!
 
Upvote 0
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: )
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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
Back
Top