sumif error

[tipoo]

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

 

[Francis]

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

 

[Francis]

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

 

[Jacob Skaria]

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,Sheet1!B:B))


If this post helps click Yes

 

[tipoo]

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