Populating column based on multiple criteria

[SpeedThink]

I am working on a Excel Spreadsheet structured as follows:

Invc No Chgs Code RejCd Invc w/Rej Sum_Invc

'2815 $35 CH
'2815 $35 CH
'2815 $35 CH
'2817 PY DED
'2817 PY
'2819 $500 CH
'2819 PY DUP

Specifically, I would like to populate the two columns on the right. Column titled "Invc w/Rej" should contain "1" or the Invc No. on each row for the Invc if there is a Rejection Code in the column titled "RejCd." The last column titled "Sum_Invc w/Rej" should only contain the sum of the charges for those invoices that have rejection codes, i.e. there is something in the column titled RejCd.

I have been unsuccessful in trying to resolve this. Initially, I tried the following array function to no avail: =MIN(if(InvcNo=A5,RejCd,10000))

Any suggestions?​

 

[TonyJollans]

Hi SpeedThink,

I have read this several times and can't quite follow what you want. To clarify, could you tell me what you expect in your two columns with the sample data you have already posted.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[SpeedThink]

Sorry about the confusion,

I will restate the problem:

Currently, I have the following Excel Worksheet
("-" are used to align data for display purposes)

Invc------- Code------Status-------------Chgs------RejCode
291--------CH--------- nopay-------------50
291--------CH--------- nopay-------------50
291--------PY----------nopay-------------------------ded
152--------CH--------- nopay-------------50
152--------CH--------- nopay-------------25
152--------PY----------nopay-------------------------dat
206--------CH--------- nopay-------------50
206--------CH--------- nopay-------------50
206--------PY----------nopay
507--------CH--------- nopay-------------50
507--------CH--------- nopay-------------45
507--------PY----------nopay-------------------------ded
600--------CH--------- over---------------25
600--------CH--------- over---------------25
600--------PY----------over---------------------------ded


The rejection codes ("ded","dat") are only on the payment record of the invoice, therefore- I don't believe that a pivot table will work.

I would like to obtain the following results, Total Charges by Rejected "nopay" invoices only and the specific "no pay" invoices with rejections as displayed below:

RejCode----Number of nopay Invcs----TotChgs
dat-------------1-------------------$75
ded-------------2--------------------$195

AND

Invc----RejCode-----TotChgs
291-----ded---------$100
152-----dat---------$75
507-----ded---------$95

The Rejection Code ("ded","dat") is only on the payment record of the invoice, therefore- I don't believe that a pivot table will work.

Is there a formula that can populate the blank cells in the column titled "RejCode" with the same RejCode for that Invoice, as already stated in that column (below or after the current cell) that is based on the contents in column "A"? In other words, the rejection code should be the same for the particular invoice.

Then, I could just use a SUMPRODUCT formula to obtain the results...

One thought is to use range names for several columns and use an array formula similar to the following:

=SUM(AND(if(InvcNo=A2,Status="no pay",RejCode<>" ",10000)*(Charges))

Another option considered is the use of the offset function in conjuction with a Sumif...

Any suggestions!

Thanks in advance.

 

[TonyJollans]

Hi SpeedThink,

I'm sure this can be done, but I don't seem to be thinking clearly at the moment. This formula (in a separate column) will populate all rows with the rejcode from the first "PY" below, provided it has the same invoice number - this should do for the data in your second post, but not your first post where you have multiple PY records for an invoice ..

[blue][tt]=IF(OFFSET(A2,MATCH("PY",B2:B$100,0)-1,0)=A2,OFFSET(E2,MATCH("PY",B2:B$100,0)-1,0),"")[/tt][/blue]

Put the formula in F2 and copy down, after changing the B$100 reference to reflect your data.

Maybe this will give you a start; I'll look at it later to see if I can do something better.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[SpeedThink]

Thank you for the insight. This pointed me in the right direction.

SpeedThink