How to combine two to three SUMPRODUCT functions in a single table?

[Nilesh Karhade]

Hi All!

Y'day I learned V Imp function i.e. SUMPRODUCT (Thnx Hui!)

But now, I hav a query, How to combine two to three SUMPRODUCT functions in a single table?

Let me elaborate. I got Prod. Qty data using Sum Prodct Functn like below


M/C No 1-Jul 2-Jul 3-Jul 4-Jul 5-Jul 6-Jul 7-Jul

3207 300 500

3222 400 400

3199 900 800


Now, I hav other parameters too which are essential for Prod. i.e. TOOLS & MATL.

I hav diffrnt dates for these 2 items. I hav to fill it up in the above table in the form of Symbols.


I tried combining Sum Prodct Functn, bt dnt ask me wht results I got! (I jst got Headache..!)

So, can anybdy plez tell me, how to do it?


well, I tried to get 3 diffrnt tables using SUm Prodct functn for each parameter separately(i.e. for Prod. Qty, TOOL Date & MATL. Date)I used digit 1 for TOOL & digit 2 for MATL. Now, I got 3 dfrnt Tables. I wntd to convert 1 & 2 in some diffrnt Symbol bt COndl. FOrmtng is not responding me. My next step wud be using AND & combining all 3 Tables.

Can Anybdy tell easy way to do it?

Thanx..!

 

[Hui]

Nilesh

Not sure that I exactly understand your question, but generally the format for Sumproduct is

=Sumproduct(+1*(Range1 <=> Cond1)*(Range2 <=> Cond2)*(Range3 <=> Cond3)*(RangeX <=> CondX),(RangeY),(RangeZ))

That is all the initial Ranges will evaluate to a 1 or a 0 and the Sumproduct will add up the sum of range Y x Range Z where all the previous conditions are true.

The good part of this is that Range 1..Range X don't have to be the same size as they end up being a 1 or 0 anyway.


If that doesn't help can you post an example somewhere

 

[Nilesh Karhade]

Gud Morng Hui!

Yaa, I tried the formula in the same way, bt didnt work bcoz my reqtmnt is somwht diffrnt.

Let me strt frm my 1st post.. I did mention abt Raw sheet(i.e. frm whr I hav to pick up the data) In my 1st post I jst mentiond a part of it. Actual sheet formt with othr details is like below:


RAW SHEET


Machine No____3207_____3222____3129____3421____3911____3702 & So on....

Prod. Qty._____400______1000____700_____300_____150_____250

Prod. Date_____4jul_____6jul_____7jul_____19jul____23jul____27jul

Tool Date______4jul_____(Text)___5jul_____(Text)___23jul____26jul

Matl.Date______3jul_____Blank____6jul_____17jul____Blnk_____27jul


So, if we see, we cnnt get summarized picture frm above frmt (Data). Actually it shud be in such a frmt which can give day wise clear picture.

So, now m giving how abve data shud b placed in a table to get quick summry.

(Below formt is what I m trying to achieve bt I get blockd in betwen. Plez hav a look.)

Symbols (x-Tool Date & * Matl. Date)


DESIRED FORMAT


M/C No.______1jul______2jul______3jul______4jul______5jul______6jul_____7jul_continue..

3207_________00_______00_______00______400X_____00________00______00____

3222_________00_______00_______00_______00______00_______1000_____00____

3129_________00_______00_______00_______00_______X________*_______700___

3421

3911

.

.

continue.


Now, I tell u how ths is diffrnt & in wht ways...


1. If Prod, Tool &/or Matl. dates match, then SUMPRODCT works & one more thing, all entries shud go in one cell only.

2. If they do not, then SUMPRODCT shows zero as atleast one condition doesnt get satisfied (Bcoz dates are diffrnt)

3. In addtn I hav to use symbols for Tool Date & Matl. Date which SUMPRODCT does not recognise.

4. In my RAW SHEET, there are some Blanks & some TEXT in Dates Row, which show #N/A or #/VALUE or ZERO whn formula is dragged.


I knw, ths POST is Lengthy, Bt cud nt explains d details without it. I Hope M clear now.


Plez suggest me any easy way. I ll b very thankful to u !

 

[Hui]

Nilesh

In your description above you say that Point 1. "If Prod, Tool &/or Matl. dates match, then SUMPRODCT..."

But in your example for M/C No 3129 none of the dates match and yet you have a value of 700 in the 7 Jul column?


Can you post your spreadsheet somewhere, with an area showing what you are trying to achieve

 

[Nilesh Karhade]

Hi Hui!

Ya, of course!

I think thr is some ambiguity. See, Desired Format is the sheet which I hav to achieve ultimately. I hav inserted the fig. for undrstnding purpose. Its not the actual result which I got from SUMPRODCT (Plez note!)

U r right, thr shud not b any result in 7th column. thats wht i alwys try to get, bt I never get. I hav mentioned the figs & symbols bcoz I wnt it that way. then only I can track on Matl., Tool & Prod. date.

So, whr can I send Spreadsheet? Any official mail ID?

 

[Hui]

Do the 3 Dates need to match or just any 2 ?


For details on posting files read:

http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[Nilesh Karhade]

Well, none of the Doc. Sharing website opens on my Company Sever (Security policy)

I ll upload the spreadsheet frm my personal net connection in the eve.


Hui, thr is nothing to match. It realy doesnt mattr if date matches one anothr or not.

Whaterer Database (i.e. Raw Sheet) contains, I jst hav to transfer it in my Desired Formt thats all. Thr is really nothing about matching dates.

 

[Hui]

Nilesh

I don't understand that a while ago you posted

"If Prod, Tool &/or Matl. dates match, then SUMPRODCT works & one more thing, all entries shud go in one cell only."

and now you say "Thr is really nothing about matching dates. "


I think you need to clarify what you are trying to achieve as the Sumproduct I sent you in a post the other day gives you the table you have as your "Desired Format" above

 

[Nilesh Karhade]

Well, I ll upload spreadsheet in the eve. & it ll get clarified wht m trying to say.

Anywys, m not gonna say anything now, bcoz its creating more confusions. let's discuss 2maro once u refer my sprdsheet.

Thnx bddy for sparing time 4 me!