How to get the Sum in a Single SUMPRODUCT maintaining the Different Criterions?

**e4excel** · 01-16-2012, 05:28 PM

I think I got it and thanks to you NBVC I just kept on trying..just added a few more conditions..

Intentionally for readablity purposes put the formula in Quotes

SUMPRODUCT((SubSrNoRng=$B2)*(PymtForRng="Regular Payments")*((SUBSTITUTE(CashToCrdt,"Amount","")=ModesRng)+(ModesRng="Cheque")*(ChqStatusRng="Cleared")),AmtRng)

Warm Regards
e4excel

**e4excel** · 01-17-2012, 02:53 AM

It worked well but I hve to share that there was an anomaly which I have not actually understood clearly but have still managed to luckily curb and still get the expected answers..


=SUMPRODUCT((SubSrNoRng=$B2)*(PymtForRng="Regular Payments")*((SUBSTITUTE(CashToCrdt," Amount","")=ModesRng)+(ModesRng="Cheque")*(ChqStatusRng="Cleared"))*(AmtRng>2),AmtRng))

First I had tried adding Blanks after each Mode but then I removed it and just added an extra Blank in between "Cheque Amount"..

Now can someone please explain me as to whats the big deal about this ,please find all the "Cheque Columns
Cheque Bank
Cheque Branch
Cheque No
Cheque Amount
Cheque Date
Cheque Deposit Date
Cheque Clearing Status

Please can someone track the anomaly..

Warm Regards
e4excel

**NBVC** · 01-17-2012, 08:04 AM

Probably all the items in CashToCrdt have an extra space?

If there is a drop down list, check that the list doesn't have extra spaces....

I know your original "Fruit" sample had trailing spaces everywhere.

**e4excel** · 01-17-2012, 08:20 AM

Actually, there's no big problem as its working but I am not understanding why thats happening?

These are the different columns with the Following Headings
---Col I to ---Col O

Cheque Bank ---Col I
Cheque Branch
Cheque No
Cheque Amount ---- Added Extra Space to make this work...between Cheque and Amount..
Cheque Date
Cheque Deposit Date
Cheque Clearing Status------Col O --- This is a Data Vaildation and gets activated only when there is cCheque mentioned in the Mode Column F..

So--- Col O is Data Validation

..=IF($F2="Cheque",Cheque_Status,"")

and the Cheque_Status is a Defined Name which has all the elements like
Cheque_Status
Cleared
Bounced
Hold
Post Dated Chq

Warm Regards
e4excel

**NBVC** · 01-17-2012, 08:23 AM

So are there 2 spaces between Cheque and Amount? If so, then check this range: ModesRng for trailing spaces.

**e4excel** · 01-17-2012, 08:25 AM

No NBVC there are NOne ..and thats the reason its strange to understand

**NBVC** · 01-17-2012, 08:30 AM

Well, again without seeing the actual spreadsheet, I can't keep guessing...

Also: I just noticed you added a poll to the thread... please don't do that unless you are asking for actual polls (I.e. doing a survey)

**e4excel** · 01-17-2012, 08:38 AM

OK I am not really aware of the use of a Poll but I just tried it for the first time..
If you want you can remove it..

View Poll Results: Was this Query useful?

Thread: How to get the Sum in a Single SUMPRODUCT maintaining the Different Criterions?

Thread Tools

Display

Re: How to get the Sum in a Single SUMPRODUCT maintaining the Different Criterions?

Re: How to get the Sum in a Single SUMPRODUCT maintaining the Different Criterions?

Re: How to get the Sum in a Single SUMPRODUCT maintaining the Different Criterions?

Re: How to get the Sum in a Single SUMPRODUCT maintaining the Different Criterions?

Re: How to get the Sum in a Single SUMPRODUCT maintaining the Different Criterions?

Re: How to get the Sum in a Single SUMPRODUCT maintaining the Different Criterions?

Re: How to get the Sum in a Single SUMPRODUCT maintaining the Different Criterions?

Re: How to get the Sum in a Single SUMPRODUCT maintaining the Different Criterions?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions