Thread: sumproduct

Hi All

I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.
I need to see how many items processed for each set per day.

Example:
http://www.amir2000.nl/1.doc

The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))

Is anyone can help?
It is quiet urgent...
Many thanks in advance,

Amir

Originally Posted by Richard Buttrey

Hi, and welcome to the forum.

Please take a moment to review the forum rules which require formulae and code to be placed within code tags. As this is your first post I'll let it go this time.

If I understand your requirement, try the following in E15, copied down and across.

=SUMPRODUCT(($I$3:$I$8<>"")*($C$3:$C$8=$D15))+SUMPRODUCT(($I$3:$I$8<>"")*($E$3:$E$8=$D15))+SUMPRODUCT(($I$3:$I$8<>"")*($G$3:$G$8=$D15))

HTH

Hi
Thank you for the quick reply and sorry, didn't notice that I need to put the formula in a code tag.

The way that you suggested it counts also from the second column of the dates and showing me that I processed 4. The correct number is 3 for set 1 and 2 for set 2.
The thing is that maybe I'm aproaching it in the wrong way.
I need it to count only once for each set number by the date column.
So for example if I processed the item on the 02/06 for set 1 and on the 03/06 I processed for the rest of the sets, I need it to show me the corrct date.
I hope it's clear enough.

Thank you,

Amir

Please explain what the conditions for counting are then....

What does this mean, exactly?

The problem is that it counts the correct amount but not with the correct
dates.

Ok, I will try to make it simple:

I have 3 columns of dates next to 3 columns of quantities.
Then I got set #: Line 1, Line 2, Line 3, etc...

In the column of the line# I put the box#.

It's possible that I get the same item in few shipments.
That is why I have 3 columns of date.

The condition should be: If the cell of the Line column is non blank, then count it as 1 in the date it has been processed.

In the example file you can see that 1 piece has been processed for line 1 on the 02/06, 1 piece for Line 2 on the 03/06 and 2 pieces for Lines 3-4 on the 06/06.

With the formula it counting the non blanks by the first date only.
It adding the pieces for lines 2,3 and 4 to the 02/06 and not to the 03/06 and 06/06.

I'm looking for a way to see how many pieces processed per day and per line.

I hope it's more clear now.

Thanks,

Amir

Do you mean?

=SUMPRODUCT((I$3:I$8<>"")*(($C$3:$C$8=$D15)*($B$3:$B$8)+($E$3:$E$8=$D15)*($D$3:$D$8)+($G$3:$G$8=$D15)*($F$3:$F$8)))

in E15 copied down and across the table

Originally Posted by NBVC

Do you mean?

=SUMPRODUCT((I$3:I$8<>"")*(($C$3:$C$8=$D15)*($B$3:$B$8)+($E$3:$E$8=$D15)*($D$3:$D$8)+($G$3:$G$8=$D15)*($F$3:$F$8)))

in E15 copied down and across the table

No, in this way it gives me the total and not by date.

Thanks,

Amir

Why don't you repost your spreadsheet showing some expected results and how they were obtained?

Originally Posted by NBVC

Why don't you repost your spreadsheet showing some expected results and how they were obtained?

I attached another example with the current formula and what I need it to show me.

Thanks for the effort!

Amir

So how do you get 2 for Line 2 on June 2?

And where do the numbers for Line 4 come from?

Originally Posted by NBVC

So how do you get 2 for Line 2 on June 2?

And where do the numbers for Line 4 come from?

On the example file in the

That's what I need from the formula to show:

I just was counting manually to show you the result that I would like to get.

Originally Posted by Richard Buttrey

I still don't see how you get 2 for line 2 on 2 June. All 5 boxes in column H have non null values, and therefore all rows are relevant, and there are 3 unique occurrences of 2nd June on rows 2,3 & 4

Similarly for Line 4 there are entries apart from K4 and hence there would appear to be a count of 2 for the 2nd June.

Can you explain what you are counting to get your answers?

Rgds

I count the quantity per date in by each date column:
If there is 1 in 02/06 - then it's going to line 1.
If there is 2 in 02/06 - then it's going to line 1 & 2.
If there is 2 in 03/06 - then it's going to line 1 & 2.
Etc...

I added a comment to the attachments that you can visualize it.
I hope when I'm uploading the file the comments stays.

Thanks,

Amir

Originally Posted by Richard Buttrey

Sorry, I still can't understand it. Can you give the cell references , i.e. A2, A3 etc which produce the totals for the various cells in the matrix. e.g.

C26 = A2 + A3 (and explain why A4 isn't included) etc....

Do the 'Line 1', 'Line 2' etc references in H1:K1 refer to row numbers in some way or are they just column titles?


Rgds

I hopethat will make order in the confusion:-)

Amir