PDA

View Full Version : RETURN COLUMN SUMPRODUCT



elsg
08-24-2013, 11:35 AM
I am using a formula to return me to column C of a tab ("PLAN2"). :think:


=SUMPRODUCT(--(PLAN2!$B$2:$B$6721=PLAN1!B2),--(PLAN2!$A$2:$A$6721=PLAN1!A2),PLAN2!$C$2:$C$6721)

PLAN1


Date
Time
Result


16/03/13
18:00:00
0,00


16/03/13
20:15:39
0,00


16/03/13
21:51:07
0,00


16/03/13
22:35:18
0,00


17/03/13
18:45:39
0,00














PLAN2


Date
Time
Value


16/3/2013
18:00:00
65.465,00


16/3/2013
18:01:00
12,00


16/3/2013
18:02:00
545,00


16/3/2013
18:03:00
787,00


16/3/2013
18:04:00
56,00


16/3/2013
18:05:00
87,00

Aussiebear
08-24-2013, 06:37 PM
Sorry, but can you be a little more specific about the issue?

elsg
08-24-2013, 07:30 PM
My PLAN1 should return........(in Column C)
but the formula is returning zero



Date
Time
Resul


16/3/2013
18:00:00
65.465,00


16/3/2013
18:01:00
12,00


16/3/2013
18:02:00
545,00


16/3/2013
18:03:00
787,00


16/3/2013
18:04:00
56,00


16/3/2013
18:05:00
87,00

p45cal
08-25-2013, 02:09 AM
Your formula works fine here but it requires that:
1. column C of PLAN2 is numeric (you seem to be in a locale that uses a comma as a decimal and a full stop as the thousands separator - is your setup that way?)
2. there is an exact match in columns A and B (test whether 2 cells with times that look the same are the same with a formula such as (in the examle you've given) in any cell:=PLAN2!B2=PLAN1!B2 which should return TRUE.)
otherwise I got a 0.

snb
08-25-2013, 06:00 AM
=SUMPRODUCT(PLAN2!$A$2:$A$6721=PLAN1!$A2,1*PLAN2!$B$2:$B$6721=1*PLAN1!$B2,P LAN2!$C$2:$C$6721)

elsg
08-25-2013, 06:27 AM
I realized that the format of the cells in column C of each guide, was different.


After making the adjustment, the formula worked.
Now I need to know how to compare column B only (H: MM)?