Countif and sumif combination problem

[tipoo]

Hi,
I have two sheets in same workbook and they appear as follows:

Sheet1 Sheet2
Column A Column B Column A Column B Column C
(PO#) ($) (Invoice#) (PO#)

111 $15,000 222223 114
112 $20,000 222223 150
Blank cell $10,000 222240 Blank cell
Blank cell $90,000 150001 113
113 $23,560 450000 113
114 $14,890 222224 113
185 $56,870 222225 Blank cell
150 $1,560 222227 185
Blank cell $97,000 150000 112

I’m using following formula in cell C2 sheet2 Column C:
=IF(COUNTIF($B$2:B2,B2)>1,"Already
Recognised",SUMIF(Sheet1!A:A,Sheet2!B2,Sheet1!B:B))

It’s basically matching PO# from sheet2 Column B and looking it in sheet1
column A and then picking up the relevant amount from sheet1 Column B and
populating it in sheet2 Column C (where formula is being used).

My problem is that once I come across a blank cell in sheet2 Column B it
adds all the blank cells from sheet1 Column A and populates in the relevant
cell under Column C sheet2. I want to populate the blank cell with a text ‘PO
Missing’ where there is a blank cell in sheet2 Column B.

Thanks in advance for your help.

 

[Sheeloo]

Try
=IF(B2="", "PO Missing",IF(COUNTIF($B$2:B2,B2)>1,"Already
Recognised",SUMIF(Sheet1!A:A,Sheet2!B2,Sheet1!B:B)))

 

[tipoo]

Great...Thanks Champ it worked.

Try
=IF(B2="", "PO Missing",IF(COUNTIF($B$2:B2,B2)>1,"Already
Recognised",SUMIF(Sheet1!A:A,Sheet2!B2,Sheet1!B:B)))