If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Error result for link formula
Hi,
I got data as follows: Date Material ($) Labour ($) 1/1/09 100 - 22/1/09 - 150 2/2/09 200 100 and I used formula =SUMIF('Material (5-1020)'!$A$14:$A$718,"=1/1/ 2008",'Material (5-1020)'!$H$14:$H$718)-SUMIF('Material (5-1020)'!$A$14:$A$718,"1/31/ 2008",'Material (5-1020)'!$H$14:$H$718) to get the result: Jan 09 Feb 09 total Material 100 100 250 Labour 200 100 300 The total amount is linked to other report The problem is when I open the file together with the report file to get the link automatically, sometimes I found the result become error like double duplication result as follows Jan 09 Feb 09 total Material 100 100 200 Labour 200 200 400 However the formula does not change at all. Could anyone help me for this problem? |
#2
|
|||
|
|||
Error result for link formula
Try the below formula instead...
To get the total of materials for the month of January..with dates in ColA and material amount in Column H.. =SUMPRODUCT(--(MONTH('Material (5-1020)'!$A$14:$A$718)=1),'Material (5-1020)'!$H$14:$H$718) To understanding the formula try with a different example in ColA and B....A with dates and B with amounts....change the 1 to 2 for Februay...Or you can even refer the month to a cell as '=MONTH(cell)' =SUMPRODUCT(--(MONTH(A1:A10)=1),B1:B10) If this post helps click Yes --------------- Jacob Skaria "tj" wrote: Hi, I got data as follows: Date Material ($) Labour ($) 1/1/09 100 - 22/1/09 - 150 2/2/09 200 100 and I used formula =SUMIF('Material (5-1020)'!$A$14:$A$718,"=1/1/ 2008",'Material (5-1020)'!$H$14:$H$718)-SUMIF('Material (5-1020)'!$A$14:$A$718,"1/31/ 2008",'Material (5-1020)'!$H$14:$H$718) to get the result: Jan 09 Feb 09 total Material 100 100 250 Labour 200 100 300 The total amount is linked to other report The problem is when I open the file together with the report file to get the link automatically, sometimes I found the result become error like double duplication result as follows Jan 09 Feb 09 total Material 100 100 200 Labour 200 200 400 However the formula does not change at all. Could anyone help me for this problem? |
#3
|
|||
|
|||
Error result for link formula
Hi Jacob,
Thank you for the quick response My next question is : can I use the same formula if the date contains of the different year? for example: Date Material ($) Labour ($) 1/1/09 100 - 22/1/09 - 150 2/2/09 200 100 31/1/10 100 21/2/10 100 Regards, "Jacob Skaria" wrote: Try the below formula instead... To get the total of materials for the month of January..with dates in ColA and material amount in Column H.. =SUMPRODUCT(--(MONTH('Material (5-1020)'!$A$14:$A$718)=1),'Material (5-1020)'!$H$14:$H$718) To understanding the formula try with a different example in ColA and B....A with dates and B with amounts....change the 1 to 2 for Februay...Or you can even refer the month to a cell as '=MONTH(cell)' =SUMPRODUCT(--(MONTH(A1:A10)=1),B1:B10) If this post helps click Yes --------------- Jacob Skaria "tj" wrote: Hi, I got data as follows: Date Material ($) Labour ($) 1/1/09 100 - 22/1/09 - 150 2/2/09 200 100 and I used formula =SUMIF('Material (5-1020)'!$A$14:$A$718,"=1/1/ 2008",'Material (5-1020)'!$H$14:$H$718)-SUMIF('Material (5-1020)'!$A$14:$A$718,"1/31/ 2008",'Material (5-1020)'!$H$14:$H$718) to get the result: Jan 09 Feb 09 total Material 100 100 250 Labour 200 100 300 The total amount is linked to other report The problem is when I open the file together with the report file to get the link automatically, sometimes I found the result become error like double duplication result as follows Jan 09 Feb 09 total Material 100 100 200 Labour 200 200 400 However the formula does not change at all. Could anyone help me for this problem? |
#4
|
|||
|
|||
Error result for link formula
Try this version...for Jan 2009. Change to suit your requirement...
(all in one line) =SUMPRODUCT(--(TEXT('Material (5-1020)'!$A$14:$A$718,"MMYYYY")="012009"),'Material (5-1020)'!$H$14:$H$718) OR ....... =SUMPRODUCT(--(TEXT(A1:A10,"MMYYYY")="012009"),B1:B10) -- If this post helps click Yes --------------- Jacob Skaria "tj" wrote: Hi Jacob, Thank you for the quick response My next question is : can I use the same formula if the date contains of the different year? for example: Date Material ($) Labour ($) 1/1/09 100 - 22/1/09 - 150 2/2/09 200 100 31/1/10 100 21/2/10 100 Regards, "Jacob Skaria" wrote: Try the below formula instead... To get the total of materials for the month of January..with dates in ColA and material amount in Column H.. =SUMPRODUCT(--(MONTH('Material (5-1020)'!$A$14:$A$718)=1),'Material (5-1020)'!$H$14:$H$718) To understanding the formula try with a different example in ColA and B....A with dates and B with amounts....change the 1 to 2 for Februay...Or you can even refer the month to a cell as '=MONTH(cell)' =SUMPRODUCT(--(MONTH(A1:A10)=1),B1:B10) If this post helps click Yes --------------- Jacob Skaria "tj" wrote: Hi, I got data as follows: Date Material ($) Labour ($) 1/1/09 100 - 22/1/09 - 150 2/2/09 200 100 and I used formula =SUMIF('Material (5-1020)'!$A$14:$A$718,"=1/1/ 2008",'Material (5-1020)'!$H$14:$H$718)-SUMIF('Material (5-1020)'!$A$14:$A$718,"1/31/ 2008",'Material (5-1020)'!$H$14:$H$718) to get the result: Jan 09 Feb 09 total Material 100 100 250 Labour 200 100 300 The total amount is linked to other report The problem is when I open the file together with the report file to get the link automatically, sometimes I found the result become error like double duplication result as follows Jan 09 Feb 09 total Material 100 100 200 Labour 200 200 400 However the formula does not change at all. Could anyone help me for this problem? |
#5
|
|||
|
|||
Error result for link formula
Hi Jacob,
Yes it works, thanks a lot By the way could you please help me with the SUMProduct fomula if we use more than 1 criteria except date criteria. The table is: Date Item Amount 1/1/09 Material - Sheet 100 2/2/09 Material - packing 150 12/2/09 Material - Sheet 100 31/3/09 Material - Other 100 I want the result : Jan 09 Feb 09 March 09 Material - Sheet 100 100 Material - Packing 150 Material - Other 100 Thanks for your attention. Regards, "Jacob Skaria" wrote: Try this version...for Jan 2009. Change to suit your requirement... (all in one line) =SUMPRODUCT(--(TEXT('Material (5-1020)'!$A$14:$A$718,"MMYYYY")="012009"),'Material (5-1020)'!$H$14:$H$718) OR ....... =SUMPRODUCT(--(TEXT(A1:A10,"MMYYYY")="012009"),B1:B10) -- If this post helps click Yes --------------- Jacob Skaria "tj" wrote: Hi Jacob, Thank you for the quick response My next question is : can I use the same formula if the date contains of the different year? for example: Date Material ($) Labour ($) 1/1/09 100 - 22/1/09 - 150 2/2/09 200 100 31/1/10 100 21/2/10 100 Regards, "Jacob Skaria" wrote: Try the below formula instead... To get the total of materials for the month of January..with dates in ColA and material amount in Column H.. =SUMPRODUCT(--(MONTH('Material (5-1020)'!$A$14:$A$718)=1),'Material (5-1020)'!$H$14:$H$718) To understanding the formula try with a different example in ColA and B....A with dates and B with amounts....change the 1 to 2 for Februay...Or you can even refer the month to a cell as '=MONTH(cell)' =SUMPRODUCT(--(MONTH(A1:A10)=1),B1:B10) If this post helps click Yes --------------- Jacob Skaria "tj" wrote: Hi, I got data as follows: Date Material ($) Labour ($) 1/1/09 100 - 22/1/09 - 150 2/2/09 200 100 and I used formula =SUMIF('Material (5-1020)'!$A$14:$A$718,"=1/1/ 2008",'Material (5-1020)'!$H$14:$H$718)-SUMIF('Material (5-1020)'!$A$14:$A$718,"1/31/ 2008",'Material (5-1020)'!$H$14:$H$718) to get the result: Jan 09 Feb 09 total Material 100 100 250 Labour 200 100 300 The total amount is linked to other report The problem is when I open the file together with the report file to get the link automatically, sometimes I found the result become error like double duplication result as follows Jan 09 Feb 09 total Material 100 100 200 Labour 200 200 400 However the formula does not change at all. Could anyone help me for this problem? |
Thread Tools | |
Display Modes | |
|
|