sumproduct ?
 

Dear All,
I have 3 cols in sht 1
Col1 Col2 Col3
XXX date1 5.00
XXX date1 (1.00)
XXX date2 3.00
YYY date1 2.00
YYY date1 6.00
YYY date2 (9.00)
In sheet 2 A1, I would like to sum the absolute net on each date of XXX,
i.e. abs(5-1)+abs(3),expected result 7.
A2 doing the same with YYY, i.e. abs(2+6)+abs(-9),expected result 17.
I think of sumproduct() on single date but dont know how to pull different
date together (dates are non-fixed and non-sequential, no of rows could be
changed).
Any suggestion please?
Regards

sumproduct ?
 

Consider using a pivot table. The advantage is that you don't need to
pre-define a list of possible XXX's or dates.
--
Gary''s Student - gsnu200909


"Seeker" wrote:

Dear All,
I have 3 cols in sht 1
Col1 Col2 Col3
XXX date1 5.00
XXX date1 (1.00)
XXX date2 3.00
YYY date1 2.00
YYY date1 6.00
YYY date2 (9.00)
In sheet 2 A1, I would like to sum the absolute net on each date of XXX,
i.e. abs(5-1)+abs(3),expected result 7.
A2 doing the same with YYY, i.e. abs(2+6)+abs(-9),expected result 17.
I think of sumproduct() on single date but dont know how to pull different
date together (dates are non-fixed and non-sequential, no of rows could be
changed).
Any suggestion please?
Regards

sumproduct ?
 

With query date in cell Sheet2 cell A1; try the below formula in cell B1

=SUMPRODUCT(--(Sheet1!A2:A10="XXX"),--(Sheet1!B2:B10=A1),
Sheet1!C2:C10)

or with query string 'xxx' in cell Sheet2 cell A1; date in cell B1 try the
below formula in cell c1
=SUMPRODUCT(--(Sheet1!A2:A10=A1),--(Sheet1!B2:B10=B1),
Sheet1!C2:C10)


If this post helps click Yes
---------------
Jacob Skaria


"Seeker" wrote:

Dear All,
I have 3 cols in sht 1
Col1 Col2 Col3
XXX date1 5.00
XXX date1 (1.00)
XXX date2 3.00
YYY date1 2.00
YYY date1 6.00
YYY date2 (9.00)
In sheet 2 A1, I would like to sum the absolute net on each date of XXX,
i.e. abs(5-1)+abs(3),expected result 7.
A2 doing the same with YYY, i.e. abs(2+6)+abs(-9),expected result 17.
I think of sumproduct() on single date but dont know how to pull different
date together (dates are non-fixed and non-sequential, no of rows could be
changed).
Any suggestion please?
Regards

sumproduct ?
 

Hi Jacob,
Thanks again for your help in the past and for now.
May be I didn't make myself clear on the problems, a simple sumproduct
cannot meet my requirement.
A1 & A2 of sheet 2 are the locations of the results.
First of all seperate XXX & YYY into two groups, then
Calculation involves all dates, some of them are unique but some of them are
not, so
1) seperate different dates into groups, sum on each group (normal adding
function)
2) disregard (+/-) value of groups sum result , make the result as ABS()
then sum
Thanks


"Jacob Skaria" wrote:

With query date in cell Sheet2 cell A1; try the below formula in cell B1

=SUMPRODUCT(--(Sheet1!A2:A10="XXX"),--(Sheet1!B2:B10=A1),
Sheet1!C2:C10)

or with query string 'xxx' in cell Sheet2 cell A1; date in cell B1 try the
below formula in cell c1
=SUMPRODUCT(--(Sheet1!A2:A10=A1),--(Sheet1!B2:B10=B1),
Sheet1!C2:C10)


If this post helps click Yes
---------------
Jacob Skaria


"Seeker" wrote:

Dear All,
I have 3 cols in sht 1
Col1 Col2 Col3
XXX date1 5.00
XXX date1 (1.00)
XXX date2 3.00
YYY date1 2.00
YYY date1 6.00
YYY date2 (9.00)
In sheet 2 A1, I would like to sum the absolute net on each date of XXX,
i.e. abs(5-1)+abs(3),expected result 7.
A2 doing the same with YYY, i.e. abs(2+6)+abs(-9),expected result 17.
I think of sumproduct() on single date but dont know how to pull different
date together (dates are non-fixed and non-sequential, no of rows could be
changed).
Any suggestion please?
Regards

sumproduct ?
 

Hi Gary,
Sorry reply to you one day late, I tried to reply to you twice on yesterday
but both were rejected saying service temporary out.
Thanks for your suggestion. However, as I am not familiar with pivot table,
I know that it has sum on grouping function but I dont know how to arrange
that. Besides, does it also able to make the sum result of each group as
absolute value (making the negative result as positive value) then total of
all groups?
Regards


"Gary''s Student" wrote:

Consider using a pivot table. The advantage is that you don't need to
pre-define a list of possible XXX's or dates.
--
Gary''s Student - gsnu200909


"Seeker" wrote:

Dear All,
I have 3 cols in sht 1
Col1 Col2 Col3
XXX date1 5.00
XXX date1 (1.00)
XXX date2 3.00
YYY date1 2.00
YYY date1 6.00
YYY date2 (9.00)
In sheet 2 A1, I would like to sum the absolute net on each date of XXX,
i.e. abs(5-1)+abs(3),expected result 7.
A2 doing the same with YYY, i.e. abs(2+6)+abs(-9),expected result 17.
I think of sumproduct() on single date but dont know how to pull different
date together (dates are non-fixed and non-sequential, no of rows could be
changed).
Any suggestion please?
Regards