sumproduct

[amir2000]

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

[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.

Please Login or Register  to view this content.

HTH

[amir2000]

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.

Please Login or Register  to view this content.

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

[NBVC]

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.

[amir2000]

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

[NBVC]

Do you mean?

Please Login or Register  to view this content.

in E15 copied down and across the table

[amir2000]

Do you mean?

Please Login or Register  to view this content.

in E15 copied down and across the table

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

Thanks,

Amir

[NBVC]

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

[amir2000]

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

[NBVC]

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

And where do the numbers for Line 4 come from?

[amir2000]

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.

[Richard Buttrey]

On the example file in the I just was counting manually to show you the result that I would like to get.

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

[amir2000]

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

[Richard Buttrey]

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

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

[amir2000]

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

[amir2000]

Anyone, any suggestions??

Thanks,

Amir

[NBVC]

You have crossposted here:

http://www.mrexcel.com/forum/showthread.php?t=328419

[amir2000]

You have crossposted here:

http://www.mrexcel.com/forum/showthread.php?t=328419

Thought it's different forum, just trying to get help.

Thanks,

Amir

[NBVC]

Your logic is not easy to follow....

If you can explain, in words, the logic that the formula should have, then perhaps someone can help...



P.S. It is a different forum that you have crossposted to... our rules specify that you must indicate that you have crossposted elsewhere...

[amir2000]

Hi,
First sorry didn't mean to break the rules.

Now,
I have 3 columns of dates.
It is possible that the 3 of them will have diferent dates.
I have 44 lines to distribute the samples.
Some of them I get in few shipments, therefor it might be processed in several dates.

What I need is that the formula will show me how many pieces I processed for each line in each day.

Next to each date column there is a quantity column.

To each line that get sample we mark it with: box #.

The formula that I'm using now showing me only by the first column of the date.
For example I got 10 pieces on the 02/06 it showing me the 02/06.
The rest I got on 03/06, it showing me that I processed it on the 02/06 instead of the 02/06.

I hope it's clear now and sorry again for not reading the rules.

Thanks,

Amir