Alter this array formula

[julhs]

I have this array formula that all works as intended when value to be retrieved is in column ‘S’, however the value may be in column ‘T’, how do I tweak this formula to retrieve it when it is.

(When ‘S’ = value, ‘T’ will always = ”” and when ‘S’= ””, ‘T’ will always = value)

Please Login or Register  to view this content.

[avk]

More helpful if you attach sample file with before (original) and after (required output).

[julhs]

Just preping one

[julhs]

Hi AVK
Have attached Workbook.

To quickly explain N9 – N23 is a small part of the INPUT section and N24 on down is Summary/breakdown

You will see in R10,11 & 16 there are payments to ’Hudson Stores’. One from the ‘Bank’ & 2 in ‘Cash’. If you now look down at W72 you will see that the BANK payment is showing but not the CASH

[avk]

as per your formula you mentioned range column "S7" to "S23" (for Bank) you cash payment in column "T". Kindly change accordingly.

[JohnTopley]

Try

=SUMIF($R$7:$R$23,$W$69,$S$7:$S$23)+SUMIF($R$7:$R$23,$W$69,$T$7:$T$23)

[julhs]

Hi AVK

Thanks for your time.

Yes that is the simplest thing to do in many ways. However I will still need a ‘TOTAL’ for ‘Cash Payments and that would be simple enough to achieve with another formula. But I was trying to see if by making alterations to the existing array I could get the desired outcome, so more an academic question really.

Any further thoughts?


Hi John,
I have a little time now to have a look at what you suggested, many thanks

[avk]

Look attach file. In this file using sumifs formula. one additon in column "N72" & "N73" cell use "DataValidation"

[julhs]

Thank you both for your help. From your suggestions have come up with a solution that works for me,

a) have a ‘Total Cash’ figure to reconcile the ‘Petty Cash’
b) and not have blank cells/missing figures in the ‘Summary/Breakdown’ columns.