Thread: SUMPRODUCT Evaluation for TRUE or FALSE for all the Meeting Criterions?

SUMPRODUCT Evaluation for TRUE or FALSE for all the Meeting Criterions?

Dear Forum,

I am using the Sumpropduct funtion and quite satisfactorily its giving me results, however I need to use it as TRUE or FALSE to categorsie certain responses and these are given by combining the SUMPRODUCT with a regular IF condition..

I would like to know whether the Criteria is met for the Entire Range or NOT.?

If it does there would be a Particular response and if not then some other, so how do I get the same..

Due to confidential reasons I am unable to post the sample workbook but can provide the code to give an Idea..

IF(SUMPRODUCT(--(SubSrNoRng=$B2)*(CashAmtRng="")*(ChqAmtRng="")*(RTGS_NEFTAmtRng="")*(CrdtCardAmtRng="")),"Cond_I","Cond_II"

In the above code there are 2 Instances where the values are "" so the answer I get is 2 but there are THREE ROWS AS per the SubSrNo Col so I want to check whether the condition matches with all the Three Rows..

So, how do I get that result, I mean is there a way of getting the Result 3 Rows and then Matching whether all the criterions are Matching for the 3 Rows.?

Warm Regards
e4excel

Thanks a lot Richard, I will try to create a dummy workbook as I have lot of queries with respect to the file...
This file maybe circulated with other lower versions of excel and therfore I chose to go with Sumproduct rather than IFs Family of Functions..
I liked the idea of the concatenation of all the criterions and reversing the logic but actually I needed to get the TRUE or FALSE in a Single Formula but maybe thats not possible , is it?

Timebeing I will use the COUNTIF and SUMPRODUCT combination but if there was something additional I could use in a single formula that would have been great as I have several instances to use such scenarios..

REgards

Dear DonkeyOte and Richard,

Please find the dummy file attached..
Actually, I am trying to update the Booking Status by explicitly entering fixed amounts in the Payment Details Sheet which will decide the Booking Status.. such as mentioned below:

There are Categories in the Booking Status for the Plots on Sale.
1. Blocked - By Paying just Re 1/- we need to change the Status to "Blocked"..This Re 1/- would not get added in the Total Payment for that Plot and the particualr Purchaser. This category is created to avoid sales conflict and protect business interest amongst Sales Team, we just add a small amount as Rs 1/- in the form of Cash to Block a Plot.
( In reality, even if there are more than 2 people who have pitched for the same Plot which was Un-Sold the one who brings in the Payment-Cheque gets the Sale in his Name )
The Moment we add the actual amount in the different Modes then it should get converted to the status as "Booked".
Maybe if possible if we could time this Status for a Duration for 3 days and then Automatically it changes to Un-Sold unless acted upon explicitly by the data entry person.


2. Booked- As explained when the Actual Payment is recorder for any Plot which was previously never Purchased or was Un-Sold is termed as "Booked". A "Booked" plot can have an existing status as "Blocked".

3. Cancelled-Un-Sold - The Client has changed his mind and wants to cancel his deal so we put a (Negative amount os - Rs 2/-) to change the Status to Cancelled-Un-Sold to have it in records that this was originally booked and now cancelled.This means that this is a PLot which was actually Sold but presently cancelled..The Rs -2/- is again entered in the Cash Amount Column to change the Status.


4. Cancelled-Booked - When any Client buys a Plot which was already cancelled in our records then it will not be fresh booking and therefore to differentiate between a fresh deal and a cancelled deal we call it as "Cancelled-Booked " So the same Plot No initially "Cancelled_Un-Sold" if Booked again would be considered as Cancelled-Booked and not just Booked.

Please note that we dont need to record the Blocked aspect as Cancelled_Blocked as "Blocked" is a terminology to make the Sales Team aware as to which Plots are already under consideration so that they can avoid pitching for the same plots but as explained earlier incase if there are 2 Different Clients dealing with 2 Different Sales Mgrs then whoever brings in the Cheque first will crack the deal..

Un-Sold - All Plots which are not "Booked" or "Cancelled-Un-Sold" by default are "Un-Sold"..

The problem I am facing is that when there are cancellations for the Plot the Plot Nos remain the same but the Purchaser's Name Changes so I need to keep a track of different deals for the same Plot Nos.

There are certain complexities such as when one client purchasers more than one Plot and cancelles one of them as well as I wanted to create a category of "Double-Deal" incase there is Double Booking for a Plot which is not cancelled..

So either I need to have a certain condition in Data Validation which prevents the entry of the Name of a different person incase there is no cancellation of that particualr Plot.

Warm Regards
e4excel

Dear DO,

Its indeed very complicated and therefore I am getting confused in seperating each category as I am unable to distinguish the unique quality of each category, when I look at it individually it gets done but when combined it fails...

I actually wanted to know whether I can count the no of instances when oe Plot is mentioned with different Client Names as mentioned the Plot No is repated twice with different Names so how can I get the answer 2?

can you please suggest a better approach if this is too complex..

Would really appreciate some help as this is Status is holding me down for a long time.

Please ..

P.S. I have already worked on the total payments concept and forgot to mention that for a cheque payment we need to select the drop-down as Cleared and for any payment we also need to select the drop-dopwn as "Regular Payments".

Regards
e4excel

Originally Posted by DonkeyOte

e4excel - if I am honest the above is sufficiently convoluted that I won't be able to come back with anything meaningful.

Dear DO,

CAn you please guide me atleast, as to how can I simplify things if possible as I actually help up on this for a long-time now..
I would be greatful if you could advise me as to how I can change wherever neccessary.

Regards
e4excel

Is it possible to get the No of Times the Plot is used for Different Names?

I am getting lost in this step..

I think I found something which gives me the naswer but not quite happy the way I am using it,

SUMPRODUCT(--(PlotNoRng=C2)*(PlotNoRng&PurcNameRng<>"")/COUNTIF(Y2:Y8,Y2:Y8))

I want to use the Same Defined Names in the

COUNTIF(PlotNoRng&PurcNameRng,PlotNoRng&PurcNameRng)

but unable to use so is there a way I could get the same result in this fashion so that I can avoid the Helper Column Y if possible..

Regards
e4excel

Dear DO,

I have used your SUMPRODUCT formula from the other thread to good efect but when I wanted to do a Running Formula and shortened the range to gradual increase like $C$2:$C2 it gives me an error in the Match Formula..

Can you please guide me on that please..

By attaiing this stp I can do away with the Helper Column Y in my Sheet..

=SUMPRODUCT(--(CCol&DCol<>""),--(MATCH(CCol&"@"&DCol,CCol&"@"&DCol,0)=(ROW($C$2:$C2)-ROW($C$2)+1)))

CCol = INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH('Payment Details'!$C$1,'Payment Details'!$1:$1,0),1),"1","",1)&"2:"&SUBSTITUTE(ADDRESS(1,MATCH('Payment Details'!$C$1,'Payment Details'!$1:$1,0),1),"1","",1)&ROW())

DCol = INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH('Payment Details'!$D$1,'Payment Details'!$1:$1,0),1),"1","",1)&"2:"&SUBSTITUTE(ADDRESS(1,MATCH('Payment Details'!$D$1,'Payment Details'!$1:$1,0),1),"1","",1)&ROW())

I dont know for some strange reason this does not work and gives an error in the Match Formula..

MATCH(CCol&"@"&DCol,CCol&"@"&DCol,0)

When I used it earlier with the PlotNoRng and PurcNameRng they were bigger Ranges but I want to do it gradually iin a running fashion..

SO please tell me why is there an error?

Regards
e4excel