sum if value in cell in a certain column starts with number

[pvp]

Hi,

I am looking for a formula which I need to retrieve values in sheet PO List, whenever the content of a cell in column Invoice number in the source sheet ERP starts with a numeric number
and shows in column N the respective PO number.
Attached Excel for illustration my problem.

To retrieve all the amounts in the source sheet which start numerically I tried to use this formula but it is not working.
I was trying the same with a Sumifs formula to no avail either.

SUMPRODUCT(ISNUMBER(--(LEFT(ERP!$P$1:$P$60000,1))),ERP!$N$1:$N$60000,D8,ERP!$A$1:$A$60000,N5,ERP!$E$1:$E$60000)


Many thanks for your help in advance
Philip

[AlKey]

Hi Philip and welcome to the forum
Could you please tell us which of these sheets is a "source" sheet and column? There are PO List, ERP, Rep and Supplierlist.
The only suitable column I found is on ERP sheet col P
In any case you may need a formula something like this

Please Login or Register  to view this content.

This will return TRUE or FALSE

[pvp]

Hi Alkey,

the source sheet is named "ERP" and the amount, if any, should be put into column "invoices" in sheet PO List.

Rgds

[JeteMc]

Try entering the following formula which is modified from the formula in post #1:

Please Login or Register  to view this content.

You are still going to get all zeros in M8:M12 because apparently none of the PO numbers match invoices. If you put in a PO number that you know matches an invoice, such as 16301173, you will get a non-zero value.
Let me know if you have any questions.

[pvp]

Hi,

I would now like to change the formula in such a way that it retrieves an amount if
the content of a cell in column Invoice number in the source sheet ERP contains the text "close" ( I am aware that in the present excel file for the PO numbers in sheet "PO List" this criterion is never fulfilled)
and shows in column N the respective PO number and in column document number text "P3".
How do i need to update the formula for this purpose?

Many thanks in advance

[JohnTopley]

Column P of "ERP" has cells containing "CLOSE PO ....." so do still need to check if Invoice Number in these cells starts with numeric?

And "column "document number" text "P3"" should be column "document type" text "P3"?

[pvp]

My notations were not clear you are right.

There is no need to check if Invoice Number in these cells starts with numeric

And "column "document number" text "P3"" should be column "document type" text "P3"

Many thanks

Philip

[JeteMc]

Try the following formula in M8 and down:

Please Login or Register  to view this content.

Let me know if you have any questions.