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

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

Dear Forum,

I came across a situation while using Sumproduct which gives me an answer however there are certain criterions which need to influence the Column to be Summed..

I was wondering that can be achieved in a single formula or not?

Please find the attachement where the SUMMING has to be done for a specific date and then the Columns to be SUmmed would be based on the selection in the column C Fruits..

FOr Ex:- Date 4-May-11 There are 4 instances where this date appears in the Data and there are 4 different fruits selected which I need to add if possible using a single formula, it can be any formula preferably SUMPRODUCT.


the four fruits selected are Oranges,APples, Bananas and Mango...

So I need the sum of the Fruits on this date and for all the above fruits..


Warm Regards
e4excel

No There is no selection required as the requirement is to be able to use the Single Column Range from the Defined Name FruitData as I require the Sum of all the Fruits falling on the selected date but the selection is done for the Fruits in the Column C so actually when the selected Fruit is Apple then for the selected date i.e. 4 May 11 I need to get the count of only the Apples in that ROw even though there are other numbers mentioned in the different Columns but they shoukd be ignored and only the respective column of selction to be considered in the final summation..

Now, since there are 4 instances of 4 May 2011 we need to actually sum 4 different columns for each row of the date 4 May 2011 and The expected answer should be 35+42+42+39 = 158

I ---- 4-May-11----Oranges -35
II ---- 4-May-11---Apples ---42
III ---- 4-May-11--Bananas--42
IV ---- 4-May-11--Mango----39

Warm Regards
e4excel

Try:

=SUMPRODUCT((SUBSTITUTE(Fruit,"Fruit","")=FData)*(Date=E17),FruitData)

I am not understanding fully.

Can you post a better sample workbook with the request outlined.. and expected results shown.

The only remaining thing is the Cleared Status is only applicable for the Cheque Mode so how would I make this Influence the result which is a combination of different Modes and there are remaining 3 Modes which do not need to have it as "Cleared".

Intentionally for readablity purposes put the formula in Quotes

SUM(SUMPRODUCT(--(SubSrNoRng=$B2)*(ModesRng="Cash")*(PymtForRng="Regular Payments")*(CashAmtRng>2),(CashAmtRng)),SUMPRODUCT(--(SubSrNoRng=$B2)*(ModesRng="Cheque")*(ChqStatusRng="Cleared")*(PymtForRng="Regular Payments"),(ChqAmtRng)),SUMPRODUCT(--(SubSrNoRng=$B2)*(ModesRng="RTGS/NEFT")*(PymtForRng="Regular Payments"),(RTGS_NEFTAmtRng)),SUMPRODUCT(--(SubSrNoRng=$B2)*(ModesRng="Credit Card")*(PymtForRng="Regular Payments"),(CrdtCardAmtRng))))

I was using such a big formula to get the answer but thanks to you that I can have it much shortened however with this remaining bit...

In the Picture there are several columns Hidden to accomodate the Columns and only the important and relevant ones are kept.
Please find this Picture as a reference

Warm Regards
e4excel

Where's the attachment?

I prefer spreadsheet sample for testing... and also what would be the expected result and why?

This is just a guess to try:

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

Just like DO in that thread, I am afraid I am lost as to your request at the moment.. I am finished anyway for the day... If you could come up with a good example showing the possibilities you would like addressed and the expected various outcomes, then I could try looking at it again tomorrow....