Excel Array Formula: Multiple Criteria Sum IF Challenge

[SpeedThink]

Currently, I have the following Excel Worksheet


Invc No Code Status Charges RejCode
291 CH no pay 50
291 CH no pay 50
291 PY no pay ded
152 CH no pay 50
152 CH no pay 25
152 PY no pay dat
206 CH no pay 50
206 CH no pay 50
206 PY no pay
507 CH no pay 50
507 CH no pay 45
507 PY no pay ded
600 CH overpaid 25
600 CH overpaid 25
600 PY overpaid ded

I would like to obtain the following results, Total Charges by Rejecte
"no pay" invoices and the specific "no pay" invoices with rejections a
displayed below:

Total Charges by Rejected "No pay" Invoices
Rej Code No of No pay Invcs Sum of Charges
dat 1 $75
ded 2 $195

AND

Specific "No pay" Invoices with Rejections
Invc No Rej Code Sum of Charges
291 ded $100
152 dat $75
507 ded $95

My initial response is to use range names for several columns and us
an array formula similar to the following:

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

Any suggestions!

Thanks in advance

 

[Ken Wright]

My initial response would be PIVOT PIVOT PIVOT!!!!!!!!!!!!!!!!!! That dataset
is crying out for a pivot table to wrap it all up and play with it.

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Failing that, though you may prefer to consider the SUMPRODUCT function which
will work much the same way, but doesn't need to be array entered. the one
caveat I would put in is that the formula you have will not exclude blank cells.
A " " is NOT the same as "" which will pick up a blank cell. the " " will
expect a space in the cell in order to match.

SUMPRODUCT syntax would be

=SUMPRODUCT((InvcNo=A2)*(Status="No Pay")*(RejCode<>"")*(Charges))*10000

which I *think* does what you are setting out to do.

 

[SpeedThink]

Ken,

I don't believe that the pivot table or the sumproduct formula wil
work. In essence, the rejection codes (RejCode) are only entered fo
the particular invoice on the payment(PY) record.

Specifically, if I was to populate the rejection code to all of th
records for the invoice, then I believe that a pivot table and/or
sumproduct formula will work.

Therefore, is there a method to fill the column titled "RejCode" tha
has blanks with a value that is in the same column conditional on th
same Invoice Number in column "A."

Another alternative that I considered is to filter the data by re
codes, copy the visible rows to another worksheet, then use vlookup
etc. However, this is not the preferred choice considering that th
worksheet exceeds 20,000 rows

 

[Ken Wright]

Sorry, getting late and brain is somehwat frazzled. I'm finding it hard to
picture exactly what you are seeing at the moment. If it's not sensitive, any
chance you could send me a sheet so I could see the data in the format you are
referring to? You'd have to take the NOSPAM out of my email address. Jonathon
Creek's on in a minute though (UK TV Detective Show), so I'll be out of action
for an hour

Just for info though, you could do what you said re filtering, select the cells
you would want to populate, do edit / Go To / Special / Visible Cells and then
paste the VLOOKUP formula in. Wouldn't have to extract anywhere else that way.