How to do a 2-Dimensional Addition if there are Multiple Columns & Rows as Criterions

[e4excel]

How to do a 3-Dimensional Addition if there are Multiple Columns & Rows as Criterions

Dear Forum,

I am trying to add several Columns of Data where there are Valid Numeric Entries and the Addition has to be done for a particular lookup value ROW-WISE and a Generic condition COLUMN-WISE.

I will try to explain this in concept before I make up a Dummy SHeet for this query, in-case if someone can still help...

I have to add the Amounts from 4 different Modes of Payments which are mentioned 4 Different Columns and these are spread over different rows as the payment is recorded Date-wise as well as Mode-Wise ..

And now all these columns are not not adjoining there are some other columns in between them too...

Now, I want to perform addition in such a way that the Addition if done from only those rows where the Lookup Value criteria is satisifed as well as from those columns where the Column Heading is " Amount"..

I have 4 different column headings such for these payment columns like
1 Cash Amount - Col H
2 Cheque Amount - Col L
3 RTGS/NEFT Amount - Col S
4 Credit Card Amount -Col U

And I have Lookup Criterion such as based on the Unique Plot No.. which is only in the COlumn B..

My thinking is to get the entire Range for addition i.e from COlum G till Col U and have such a condition where only the arrays of those columns which are having the last word as " Amount" to be added and which also fulfill for the particular Unique Plot NO..

I don not know whether this is possible or not..?
If yes then please can someone help me..

Example:

There are just 2 Rows which are fulfilling the conditions and the rows are ROw 2 and Row 5 and then I want all the nOs from the below
1 Cash Amount - Col H
2 Cheque Amount - Col L
3 RTGS/NEFT Amount - Col S
4 Credit Card Amount -Col U

to be added so I dont want to add 4 different col ranges but want it to be done automatically though the entire range is given from G:U

Is this possible?

Warm REgards
e4excel

[zbor]

Can you upload example?

[e4excel]

Hey Zbor,

I was not able to make the Dummy..

PFA..

I want to add the Amoounts Paid for one client with the same name and the same plot no which is assigned Unique no in the coluumn B..

For all payments to be considered it needs to be mentioned as Regular Payments" in the COl V and only for the Mode = Cheque we need to also check whether the cheque was cleared..

In one row, i.e at one time only one payment is to be considered whichever is mentioned in the column Payment Modes in COl F so even if there are 2 amounts the one which is mentioned in the Column F has to be added in the final Amount..

Ex:
F2= Cash---so considere the payment which is mentioned under cash amount even if someone erroneously enters the amount in any other column other than cash amount..

Warm Regards
e4excel

[e4excel]

I think I got the answer myself however there's one thing which I still need help on..

My code:

=SUMPRODUCT(($B$2:$B$16=$B2)*($V$2:$V$16="Regular Payments")*(SUBSTITUTE($G$1:$U$1," Amount","")=$F$2:$F$16)*($G$2:$U$16>2)+($F$2:$F$16="Cheque")*($O$2:$O$16="Cleared"),$G$2:$U$16) 

It works but I have to negate one Cheque Condition in order to get it just once, I do it by putting an extra space in the column heading
Col L "Cheque--Amount"

BUt can someone help me how do i just make a few changes in my formula so that it takes the amount for cheque only once and also maintains the logic for other modes..

The Condition to check fot the Clearing Status is only applicable when the payment is received by Cheque and not otherwise whereas the other conditions such as the UniqueSrNo Matching as well as Regualr Payments and the Amount greater than Rs 2 is applicable to all Modes.

My formula gives me an accurate answer only if I negate one Cheque Payment and I do it by making it not match the criteria.
I find this very strange and it came as a fluke to me..
I need one single formula and a uniform formatting...like having the same spaces between the words for the Column Headings containing the word "Amount"..

In order to make it different I negate the condition for the Column heading by adding a space and then based on the OR condition it meets the criteria only by the Mode & Cleared Status..

So can someone suggest me something better...

[e4excel]

Dear Forum,

Just need to know, how do I make an IF condition inside a SUMPRODUCT FUNCTION.

Like in my case I need to focus on all the 4 Columns for Amounts however only when the Mode is Cheque , it needs to be checked whether its status is "cleared" or not..

So How DO I seperate this for the Exclusive Cheque Mode and also factor it only once when the status indeed is "Cleared"..

Warm regrds
e4excel

[e4excel]

WHile checking the formula again I jsut realised that when I put payments in all the 4 Modes for one single transaction it SUMS all the payments which is incorrect I need it to consider the payment which is only mentioned by the Mode in the COL F..

If i have lets say 4 X Rs 1000 in all the Four Modes for one record i.e. ROW and the Mode is Cash then The total payment should not be Rs 4000 but just Rs 1000 which is for the Cash Mode Payment.

[e4excel]

How do I make the Mode Chq be counted only once ?

[e4excel]

Am trying to post a slightly better example for understanding

[e4excel]

Dear Forum,

I got the solution myself after satying awake the entire night pondering about the Exclusive OR & AND conditions.

=SUMPRODUCT(--($B$2:$B$16=$B2)*($V$2:$V$16="Regular Payments")*($G$2:$U$16>MinAmt)*(((SUBSTITUTE($G$1:$U$1," Amount","")=$F$2:$F$16)*((($F$2:$F$16<>"Cheque")*($O$2:$O$16<>"Cleared"))+(($F$2:$F$16<>"Cheque")*($O$2:$O$16="Cleared"))))+((SUBSTITUTE($G$1:$U$1," Amount","")=$F$2:$F$16)*($F$2:$F$16="Cheque")*($O$2:$O$16="Cleared"))),$G$2:$U$16) 

I would appreciate if still someone could better this code incase it can be made more compact..

Warm REgards
e4excel

[e4excel]

Improvised further

=SUMPRODUCT(--($B$2:$B$16=$B2)*($V$2:$V$16="Regular Payments")*($G$2:$U$16>MinAmt) * (((SUBSTITUTE($G$1:$U$1," Amount","")=$F$2:$F$16)*(($F$2:$F$16<>"Cheque") *( ($O$2:$O$16<>"Cleared")+($O$2:$O$16="Cleared"))))+((SUBSTITUTE($G$1:$U$1," Amount","")=$F$2:$F$16)*($F$2:$F$16="Cheque")*($O$2:$O$16="Cleared"))),$G$2:$U$16) 

[e4excel]

Further Improvisation...

=SUMPRODUCT(--($B$2:$B$16=$B2)*($V$2:$V$16="Regular Payments")*($G$2:$U$16>MinAmt)*(SUBSTITUTE($G$1:$U$1," Amount","")=$F$2:$F$16)*((($F$2:$F$16<>"Cheque") *( ($O$2:$O$16<>"Cleared")+($O$2:$O$16="Cleared")))+(($F$2:$F$16="Cheque")*($O$2:$O$16="Cleared"))),$G$2:$U$16) 

This is perhaps the shortest which I could come up with...

All the conditions such as are common

Please Login or Register  to view this content.

No these are the other conditions which are for Any other Mode other than Cheque With/Without "Cleared" mentioned in the Status - A

Please Login or Register  to view this content.

This is the condition for the Exclusive Cheque Mode which needs to have the Status as Cleared to be considered. - B

Please Login or Register  to view this content.

The above two lines of code i.e. A & B are inside one single parenthese so as to evaluate one of them..

Please Login or Register  to view this content.

SO I think this issue is over...!

Warm Regards
e4excel