| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumif error
Hi Guys,
I have two sheets in an excel 2003 file. Sheet 1 has purchase order# and relevant amounts. There can be more than 1 line record for same PO# on sheet 1. Just as an example would look like below: Col A Col B PO# Amount 2305675 50 2305675 50 2305897 23 2564111 4 56 2305675 200 2564111 450 Sheet 2 has invoice numbers in Col A and PO# in Col B and say amount in Col C somewhat looks like as below: ColA ColB Col C Col D Inv# PO# Amount Rev Recog 456 2305675 50 300 456 2564111 56 454 466 2305675 235 300 888 2564111 456 454 2275 2305897 545 23 In Col D above when I use =SUMIF(Sheet2!A:A,Sheet1!B9,Sheet2!B:B) where criteria is PO# from sheet 2 Col B, it adds up value of each PO if I have more than 1 occurance of same PO# (for e.g., 2305675 has 300 against it on sheet 2 while it only has 300 once in sheet 1. Is it possible to kill this problem by one formula. Please accept thanks in advance for your help. Tipoo |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif error
Hi
I am not following very well on your request are you trying to find the total for PO# 2305675 in Col B of Sheet1 and have it shown in col D in Sheet2 ? --- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "tipoo" wrote: Hi Guys, I have two sheets in an excel 2003 file. Sheet 1 has purchase order# and relevant amounts. There can be more than 1 line record for same PO# on sheet 1. Just as an example would look like below: Col A Col B PO# Amount 2305675 50 2305675 50 2305897 23 2564111 4 56 2305675 200 2564111 450 Sheet 2 has invoice numbers in Col A and PO# in Col B and say amount in Col C somewhat looks like as below: ColA ColB Col C Col D Inv# PO# Amount Rev Recog 456 2305675 50 300 456 2564111 56 454 466 2305675 235 300 888 2564111 456 454 2275 2305897 545 23 In Col D above when I use =SUMIF(Sheet2!A:A,Sheet1!B9,Sheet2!B:B) where criteria is PO# from sheet 2 Col B, it adds up value of each PO if I have more than 1 occurance of same PO# (for e.g., 2305675 has 300 against it on sheet 2 while it only has 300 once in sheet 1. Is it possible to kill this problem by one formula. Please accept thanks in advance for your help. Tipoo |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif error
Is this what you are after
place this in D2 of Sheet2 =SUMIF(Sheet1!A2:A7,Sheet2!B2,Sheet1!B2:B7) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "tipoo" wrote: Hi Guys, I have two sheets in an excel 2003 file. Sheet 1 has purchase order# and relevant amounts. There can be more than 1 line record for same PO# on sheet 1. Just as an example would look like below: Col A Col B PO# Amount 2305675 50 2305675 50 2305897 23 2564111 4 56 2305675 200 2564111 450 Sheet 2 has invoice numbers in Col A and PO# in Col B and say amount in Col C somewhat looks like as below: ColA ColB Col C Col D Inv# PO# Amount Rev Recog 456 2305675 50 300 456 2564111 56 454 466 2305675 235 300 888 2564111 456 454 2275 2305897 545 23 In Col D above when I use =SUMIF(Sheet2!A:A,Sheet1!B9,Sheet2!B:B) where criteria is PO# from sheet 2 Col B, it adds up value of each PO if I have more than 1 occurance of same PO# (for e.g., 2305675 has 300 against it on sheet 2 while it only has 300 once in sheet 1. Is it possible to kill this problem by one formula. Please accept thanks in advance for your help. Tipoo |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif error
There is some confusion..I understand that
Sheet1 has 2 cols PO# and Amount Sheet2 has 4 cols Inv,PO,Amount,RevRecog Then the current formula in Sheet2 Col D should be =SUMIF(Sheet1!A:A,B1,Sheet1!B:B) which is different from what you have specified below. If you want to ignore the duplicates try this formula in Sheet2 Cell D1 and copy that down =IF(COUNTIF($B$1:B1,B1)1,"",SUMIF(Sheet1!A:A,B1,S heet1!B:B)) If this post helps click Yes --------------- Jacob Skaria "tipoo" wrote: Hi Guys, I have two sheets in an excel 2003 file. Sheet 1 has purchase order# and relevant amounts. There can be more than 1 line record for same PO# on sheet 1. Just as an example would look like below: Col A Col B PO# Amount 2305675 50 2305675 50 2305897 23 2564111 4 56 2305675 200 2564111 450 Sheet 2 has invoice numbers in Col A and PO# in Col B and say amount in Col C somewhat looks like as below: ColA ColB Col C Col D Inv# PO# Amount Rev Recog 456 2305675 50 300 456 2564111 56 454 466 2305675 235 300 888 2564111 456 454 2275 2305897 545 23 In Col D above when I use =SUMIF(Sheet2!A:A,Sheet1!B9,Sheet2!B:B) where criteria is PO# from sheet 2 Col B, it adds up value of each PO if I have more than 1 occurance of same PO# (for e.g., 2305675 has 300 against it on sheet 2 while it only has 300 once in sheet 1. Is it possible to kill this problem by one formula. Please accept thanks in advance for your help. Tipoo |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sumif error
Hi Jacob,
Fantastic...!it worked perfectly fine. Sorry to confuse you with a typo on my initial post where I got sheet 2 mixed up with sheet 1. My range for sumif was in sheet 2 and criteria in sheet 1. Here what I got when I used ur formula (see result in column E). I used ur formula with slight midification in Col E which was: =IF(COUNTIF($B$2:B2,B2)1,"Already Recognised",SUMIF(Sheet2!A:A,Sheet1!B2,Sheet2!B:B) ) Col A Col B Col C Col D Col E Inv# PO# Amount Rev Recognised Suggested Formula Result 456 2305675 50 300 300 456 2564111 56 454 454 466 2305675 235 300 Already Recognised 888 2564111 456 454 Already Recognised 2275 2305897 545 23 23 Thanks Jacob and Francis for your help. Cheers Tipoo "Jacob Skaria" wrote: There is some confusion..I understand that Sheet1 has 2 cols PO# and Amount Sheet2 has 4 cols Inv,PO,Amount,RevRecog Then the current formula in Sheet2 Col D should be =SUMIF(Sheet1!A:A,B1,Sheet1!B:B) which is different from what you have specified below. If you want to ignore the duplicates try this formula in Sheet2 Cell D1 and copy that down =IF(COUNTIF($B$1:B1,B1)1,"",SUMIF(Sheet1!A:A,B1,S heet1!B:B)) If this post helps click Yes --------------- Jacob Skaria "tipoo" wrote: Hi Guys, I have two sheets in an excel 2003 file. Sheet 1 has purchase order# and relevant amounts. There can be more than 1 line record for same PO# on sheet 1. Just as an example would look like below: Col A Col B PO# Amount 2305675 50 2305675 50 2305897 23 2564111 4 56 2305675 200 2564111 450 Sheet 2 has invoice numbers in Col A and PO# in Col B and say amount in Col C somewhat looks like as below: ColA ColB Col C Col D Inv# PO# Amount Rev Recog 456 2305675 50 300 456 2564111 56 454 466 2305675 235 300 888 2564111 456 454 2275 2305897 545 23 In Col D above when I use =SUMIF(Sheet2!A:A,Sheet1!B9,Sheet2!B:B) where criteria is PO# from sheet 2 Col B, it adds up value of each PO if I have more than 1 occurance of same PO# (for e.g., 2305675 has 300 against it on sheet 2 while it only has 300 once in sheet 1. Is it possible to kill this problem by one formula. Please accept thanks in advance for your help. Tipoo |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF using #N/A error as criteria | Excel Discussion (Misc queries) | |||
| #VALUE! error when using UNC path name in SUMIF | Excel Discussion (Misc queries) | |||
| SUMIF Value error | Excel Worksheet Functions | |||
| Error with SUMIF but not SUM(IF) | Excel Worksheet Functions | |||
| If and nested Sumif error | Excel Worksheet Functions | |||
Linear Mode
