• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT giving unexpected Results...

Faseeh

Faseeh

Excel Ninja
Dear All,


I am stuck with this work sheet and I need your help!!


This sheet records the in/out frequency for electronic cards repaired in my factory. Every "Card" has a "S.No" and the "Out Date" and "In Date" is recorded against them followed by "Status".


First i used to use pivot table to get the summary report, but i was unable to handle text entries (Status) in it hence the report remained incomplete.


Then i adopted this format to prepared the report.


The formula i am using is:


=(INDEX(F:F,SUMPRODUCT((($D$3:$D17)=1)*(($E$3:$E17)=$J3)*(ROW($F$3:$F17))


The problem with the formula is that when it counts for the second turn, the sumproduct again gives the same result as in "turn 01" if "turn 02" is absent.


The issue i have diagnosed is that the True/False array produced by multiplication of D3:D17 and E3:E17 is responsible for doing this as it True/False multiplication results in false always. (If this is not that really happens then kindly help me understand)


Kindly help me settle this formula so that if both the criteria are meet,the result is shown only then, otherwise cell should remain blank.


Here is the file...

http://www.2shared.com/file/_SaAYDEC/Inverters__Jan_12_.html


Thanks.


Faseeh
 
Faseeh


A few queries


Your Formulas in Columns 'Enter Data'!K:V are looking up 14 rows ahead, is that right ?

eg: K2: refers to Rows 2:16,
Code: 
=IF(ISBLANK(INDEX(F:F,SUMPRODUCT((($D$2:$D16)=1)*(($E$2:$E16)=$J2)*(ROW($F$2:$F16))),))=TRUE,"-",INDEX(F:F,SUMPRODUCT((($D$2:$D16)=1)*(($E$2:$E16)=$J2)*(ROW($F$2:$F16))),))


Specifically which cell isn't working, as none have the formula or range listed above?
 
Hui,


Thanks for reply,


Actually I was using "Evaluate Formula" to check out which thing is going wrong, there fore i reduced from K2:K16 other wise it had to look for upto the last entry made (K2:K130).


The problem starts when i copy this formula to N6, it should result in a blank cell because the card this cell is refering to has no 2nd turn (see data in E6). So this is problem.
 
Hi, Faseeh!

I beg your pardon for haven't read your post before, sorry you've been waiting, but if I'm allowed to adduce on my behalf I must say I was working. Am I allowed?

Now that I'm taking a break of a couple of minutes, I opened your uploaded file and...

Questions:

a) Row 2, Card 340404324 (E), Card 1130463899 (J), In Date is greater than Out Date?

b) What is Card? Why does it differs from column E to J?

c) Turn in column D can be only 1 thru 4, so you have 4 groups of 3 columns from K to V?

d) If we take the example of Card 340404324 in column E, 4 rows 2 thru 5, the data displayed in columns K to K is correct?

e) The issue arises only (first case) for example in N6, that shows same data for turns 2, 3 and 4, in columns N to V, and there it must display blanks?

I'll go on working and in the next break I'll try to read your answer and understand a little more your issue.

Regards!
 
@ SirJB7


I am sorry if you found my last post offending, did't meant any thing like that. It was just a humble reminder. I found your reply on some other posts so i though i should put up a reminder. :)


Your Question:


a. In Date is greater then out date: Yes that might be a typographical mistake, perhaps the 3rd, 4th entry in column E was some other no, while i was trying on my own, i copied the same no. from row2 to row4. To sum up in date must always be greater then out date.


b. What is Card: Card is actually "Electronic Circuit Board" send outside for repair. Here i used term "Card" for it. What i need to show in this report is that how many times a card has been repaired and send outside. (Now i should solute my boss who is unable to understand things in shape of a "list" consolidated under each card. :p

Hence there are 3 columns per turn (each time send outside), If send four times, will have 3 columns multiplied by 4 times i.e. 12 columns.


c. Turn in column D can be only 1 thru 4: No, it could go, lets say up to 10, I will have to copy all the three columns the times it goes out.


d. For Example of 40404324 in column E: The Data is displayed is correct.


e. The issue arises only (first case) for example in N6: It could arise any where when the "Card" is sent outside only once, in that case, it must show a blank in the report. If you examine the columns M to O you will find that most of the card are send only once but they are show again with same in/out dates here again.


....and once again my sincere apologizes. :)


Regards,


Faseeh
 
Hi, Faseeh!

Nothing like offending, just a little of humour, seriously ;)

Looking more carefully to the data displayed in columns K to V for previous d) question, I think that it's only correct for row 2 (turn 1, and Card(J)=Card(E)). So your problem isn't only beginning in N6, it's already in K3 (or N3, perhaps, it just depend on the definition of columns K to V, as asked in previous c).

Am I wrong?

Regards!
 
Yes you are right, but i filter column E for unique values and paste it in place of J, then it will go OK. I have tried here..it is giving correct results. But yes, the second turn is not display relevant data even then.
 
Hi, Faseeh!

a) Ok, as I guessed

b) I still don't understand the difference between Card in column E and Card in column J. Why do they differ?

c) Ok, as I supposed

d) Sure? Review my previous post, please, and if it's incorrect, please write down here the correct data for K3:V6

e) Idem d)

Regards!

PS: I'll go on working while I leave Faseeh waiting :))) ... and thinking, and checking, ... :)))
 
Hi, Faseeh!

My two very first and only questions should have been:

1) why column E and J differ?

2) what do you want to display (example) in K3:V6?

With this I think I'll finish to understand your issue. The second question will help me get the reason for having n-groups of 3 columns for n card shipments, that are in sequential order in n consecutive rows.

Do you understand what I mean?

Regards!
 
Hi Sir, :p


I think i have been once again unable to explain. in column E, I enter data manually. The data is sorted and applied with countif() formula to find no. of turns in column D.


In Column J i need only unique values from column E, so that i may write against each of them the date the are send out, the date they arrived back and the status of theirs. If they are out for lets say 4 times, i will do the same for four times.


I mentioned in my first post that to do this, i used firstly pivot table report, you can find it on sheet: Pivot Table. The two disadvantages there were that i was unable to mention "status" that is text in it, other was that i was eager do things with formula, testing my excel-skills. hehehehe


P.S: There are chances of Electric Load Shedding, so if you find me offline or not responding, just keep going with your work :p, I hope you will come up with solution. :)
 
Hi, Faseeh!

Your awaiting don't let me concentrate in my working... ;)

I was reading even more carefully your data, specifically Card numbers, and I found this interesting points. Please correct me if I made any mistake. Just focus in columns E and J.

- from row 6 to 32, Card(E)=Card(J) and unique

- from row 33 to 34, Card(E) not unique, first round 2

- from row 34 to 48, Card(E)=Card(J-1), the previous one

- from row 49 to 58, Card(E)=Card(J-2), second round 2, the previous previous one

And so on...

Now I'm still more confused about Card from column E and Card from column J.

Please help me!

Regards!
 
Hi, Faseeh! (hope I don't interrupt his awaiting time)

My comments to your previous post:

- if the first main issue was the text data of Status, why don't replace "Reject" by 1 and blanks by 0?

- the data is sorted so as to apply the COUNTIF formula, but it's sorted only for Card(E) I guess, because the Turn in column D aren't in chronological order: for 340404324, turn 1 should be Aug 10, 2 Aug 29, 3 Sep 15 and 4 Sep 28... then, shouldn't it be sorted by Card(E) and OutDate and ShipmentReference (maybe what you call Card(J), see next point)? ... right or wrong?

- Card(J) shouldn't it reflect each shipment/envoy reference for Card(E) that is the code or serial number o EIN of the electronic circuit board?

Regards!

(while I now wait for another crossed or overstepped post)
 
Hi SirBJ


1) why column E and J differ?


Column E is part of a list. The "List" spans from Column B to Column H. The data is entered manually in it. It is then sorted by Card and then By Out Date.


2) what do you want to display (example) in K3:V6?


Take Example of E6, This shows a Card #, K6 must display 09-Jan-12, L6:V6 should not display any thing at all, only blank cells.


See this pic that shows what does i want from this formula:

http://www.4shared.com/photo/qs5kSohB/Faseeh.html
 
Hi, Faseeh!

Got it!

Just now, later than I expected, very later indeed... Would you believe that only with your pic I realized that Card(J) was a unique list of Card(E)? And I supposed it should be a reference for each shipment... Unforgivable!

'til my next break :)

Regards! (you can wait in the meanwhile)
 
Hi, Faseeh!


Check the uploaded file:

http://www.2shared.com/file/yHiGm__Y/Inverters__Jan_12__-_SUMPRODUC.html


I believe it works as you need.


Considerations:

- The solution was asking for the sumproduct value equal zero and not for the index value equal blank

- I added a formula (int((column()-8)/3) just for avoid changing the turn number and simplifying things when copying formulas from columns K:M to N in advance

- I'll try to get to one unique formula to just copy and don't even have to change the index column from F:F to G:G or to H:H... in the meanwhile you could stop waiting and analyze the file I sent you :)

- I applied format dd-mmm-yy;;;@ to columns K:V (just planning to succeed on previous item)

- Only for testing purposes: blank = data absent, "." = data without turn, "-" = no card data (if you want, you can change all to blank, dot, or your minus sign


Question: what about Card(J) for rows 3 to 5? they aren't in Card(E), that's right?


Regards!
 
Hi, Faseeh!


Stop waiting and read, please :)


In K2 cell, change the formula to this one:


=SI(ESBLANCO($J2);"-";SI(SUMAPRODUCTO((($D$2:$D$130)=ENTERO((COLUMNA()-8)/3))*(($E$2:$E$130)=$J2)*(FILA($F$2:$F$130)))=0;".";INDICE($F:$H;SUMAPRODUCTO((($D$2:$D$130)=ENTERO((COLUMNA()-8)/3))*(($E$2:$E$130)=$J2)*(FILA($F$2:$F$130)));RESIDUO(COLUMNA()-10;3)+(1-SIGNO(RESIDUO(COLUMNA()-10;3)))*3))) -----> in english: =IF(ISBLANK($J2),"-",IF(SUMPRODUCT((($D$2:$D$130)=INT((COLUMN()-8)/3))*(($E$2:$E$130)=$J2)*(ROW($F$2:$F$130)))=0,".",INDEX($F:$H,SUMPRODUCT((($D$2:$D$130)=INT((COLUMN()-8)/3))*(($E$2:$E$130)=$J2)*(ROW($F$2:$F$130))),MOD(COLUMN()-10,3)+(1-SIGN(MOD(COLUMN()-10,3)))*3)))


Then copy K2 and paste into K2:V130 (or Y or AAB, or the additional groups of three columns you want for further turns).


Et voilá! Just one formula, for multiple turns, form multiple and different data types (date and string, i.e.). I used the reference form of the index function (which permits a column offset) instead the matrix form previous.


Good luck, and if there's anything you don't understand just ask me.

Regards!


(now I've got no other choice than go on working...)
 
Hi SirJB7 & Good Morning,


I am sorry for while you were working and pondering and were facing disturbance from work, I was sleeping.. :p


There is no option to add pic to the post, i have seen it on some other excel page. Should it have been, the problem had been easier to understand.


Your workbook is working like a charm, I will pick up my pen and paper to evaluate manually what really happens in your formula. And the last post that provided a single formula i haven't checked yet. Just in order to give you a pulse of reply, I logged on to say Thanks!!


Should i always contact you (for this post) via this thread or i can use your email address?


Any ways thanks a lot and i will revert if i found some thing going beyond my level of understanding. :)


Take Care,


Faseeh
 
Hi, Faseeh!

Payback's coming... just been sleeping while you were working ;)

I recommend you to replace the single formula in K3:V130, if you'll have to make any adjustments it'd be easier.

You're free to contact me via posts/threads (better a new one for each new topic) or via e-mail.

Welcome back whenever you need.

Regards!
 
Hi SirJB7


Your Second formula is also working awesome and i have replaced the entire sheet with this formula... :)


In your third-last post you said under consideration: - "The solution was asking for the sumproduct value equal zero and not for the index value equal blank".. I am unable to pickup what this really means. Can you explain it for the original (but incorrect) formula i was us using, which part of the formula was erroneous? Here it is:


=IF(ISBLANK(INDEX(F:F,SUMPRODUCT((($D$2:$D16)=1)*(($E$2:$E16)=$J2)*(ROW($F$2:$F16))),))=TRUE,"-",INDEX(F:F,SUMPRODUCT((($D$2:$D16)=1)*(($E$2:$E16)=$J2)*(ROW($F$2:$F16))),))


Thanks,


Faseeh
 
Hi, Faseeh!


Let's split your formula in several lines.


function ................. =IF(

condition test............ ISBLANK(INDEX(F:F,SUMPRODUCT((($D$2:$D16)=1)*(($E$2:$E16)=$J2)*(ROW($F$2:$F16))),))=TRUE,

if true .................. "-",

if false ................. INDEX(F:F,SUMPRODUCT((($D$2:$D16)=1)*(($E$2:$E16)=$J2)*(ROW($F$2:$F16))),))


Let's focus on condition test and do the same.


function ................. ISBLANK(

argument test criteria ... INDEX(F:F,SUMPRODUCT((($D$2:$D16)=1)*(($E$2:$E16)=$J2)*(ROW($F$2:$F16))),))

argument test condition .. =

argument test value ...... TRUE


Repeat for arguments test criteria.

function ................. INDEX(

range .................... F:F,

index .................... SUMPRODUCT((($D$2:$D16)=1)*(($E$2:$E16)=$J2)*(ROW($F$2:$F16)))


And here it was the problem: you were checking for the value returned from the INDEX function (always a value, wrong but a value) instead of checking for the index value itself (SUMPRODUCT result) used to return the element from the range.


So I checked only for zero in SUMPRODUCT and not for blank in INDEX.

Hope it's clearer now.


Regards!
 
Hi, SirJB7


Now understood what was going wrong. You not only helped me in setting up this sheet but also let me learn a new things. Bundle of Thanks for both... :p


Stay Blessed, Regards,


Faseeh
 
You must log in or register to reply here.
Back
Top