[HELP] Auto Display Total Sum according to Date

[Coconutwei]

CUST PRODUCT PACKING DATE QTY 1/14 2/14 3/14 4/14 5/14 6/14 7/14 8/14
PBMW02 ACCU CHEK ADV 2 STRIP 25'S Jan-14 2
PBMW02 ACCU CHEK ADV 2 STRIP 25'S Feb-14 1
PBMW02 ACCU CHEK ADV 2 STRIP 25'S Feb-14 1
PBMW02 ACCU CHEK ADV 2 STRIP 25'S Mar-14 2
PBMW02 ACCU CHEK ADV 2 STRIP 25'S Apr-14 1
PBMW02 ACCU CHEK ADV 2 STRIP 25'S May-14 2
PBMW02 ACUGESIC TAB 50MG (BLIS) 10X10'S Jan-14 1
PBMW02 ACUGESIC TAB 50MG (BLIS) 10X10'S Jul-14 1
PBMW02 ADROTEN 5MG TAB 10X10'S Jan-14 1
PBMW02 ADROTEN 5MG TAB 10X10'S Apr-14 1
PBMW02 ALATROL TAB 10MG (W) 10X10'S Jan-14 5
PBMW02 ALATROL TAB 10MG (W) 10X10'S Jan-14 5
PBMW02 ARCOXIA 90MG TAB 10X10'S Apr-14 1
PBMW02 ARISONE CREAM 1% 50G May-14 2
PBMW02 ARISONE CREAM 1% 50G Jul-14 5
PBMW02 ASTHALIN INHALER 200D Feb-14 5
PBMW02 AT CHLORPYRIMINE INJ 10ML 1VL Jan-14 5
PBMW02 AT CLOFEC INJ 75/3ML 50AMP Feb-14 0
PBMW02 AT DEXASONE INJ 5MG/ML 50AMP Jul-14 0
PBMW02 AT DEXASONE TAB 50X10'S Jan-14 2
PBMW02 AT DEXASONE TAB 50X10'S Feb-14 1
PBMW02 AT DEXASONE TAB 50X10'S Jul-14 2





Greeting to all the Excel Programming Expert,

Long time ago, I received some helps on Excel formula here. I thanks them.
Today, while I was doing this, I thought of this forum, and I believed there must be something that can be done.

Above is spreadsheet of what I am currently doing.
I have a very large database of invoices, and I was asked to put the ordered quantity into each column of Month/Year.
What I am currently doing is, I manually type one by one, but I believe there must be something can be done to ease this pain. (For your info, there are around 40k of records)

I appreciate if anyone can tell me if there is a formula to be used.
The complexion of this is beyond my Excel knowledge.

What I need is,
- If the buyer bought the item on Feb, Mar, Apr, with 1-1-2 each, then the column below 2/14, 3/14, 4/14, will appeared with number 1,1,2 each.
- If there are 2 purchases over the same item on the same month, assuming Apr, 2+2, then the column will display 1,1,4 each.



Thanks in Advanced,
Coconutwei


UPDATED

If it is possible, can you try to put the formula in Excel, and upload here?

Anyway, I attached 2 spreadsheet, (v3-1) before, and (v3-2) expected results.
The best is, they are both on separated worksheet.
#1 sheet collect all the information on #2 sheet.
Any repeated information on #2 sheet, will only display on #1 sheet once.

[BadlySpelledBuoy]

Try the below in cell G2 then copy right and down as needed.

=SUMIFS($F:$F,$E:$E,">="&N$1,$E:$E,"<="&EOMONTH(N$1,0),$B:$B,$B2,$C:$C,$C2,$D:$D,$D2)

Does that do what you mean?

BSB.

[Coconutwei]

Try the below in cell G2 then copy right and down as needed.

=SUMIFS($F:$F,$E:$E,">="&N$1,$E:$E,"<="&EOMONTH(N$1,0),$B:$B,$B12,$C:$C,$C12,$D:$D,$D12)

Does that do what you mean?

BSB.

Nope, it doesn't.
The results pop out with all #NAME?

[BadlySpelledBuoy]

Sorry, didn't notice you were using Excel 2003. COUNTIFS doesn't work in versions prior to 2007.

Try:

=SUMPRODUCT(($E$1:$E$40000>=N$1)*($E$1:$E$40000<=EOMONTH(N$1,0))*($B$1:$B$40000=$B2)*($C$1:$C$40000=$C2)*($D$1:$D$40000=$D2),$F:$F)

Adjust the 40000 to fit your actual last row number.

It will be slow, but it should work.

[Coconutwei]

Sorry, didn't notice you were using Excel 2003. COUNTIFS doesn't work in versions prior to 2007.

Try:

=SUMPRODUCT(($E$1:$E$40000>=N$1)*($E$1:$E$40000<=EOMONTH(N$1,0))*($B$1:$B$40000=$B2)*($C$1:$C$40000=$C2)*($D$1:$D$40000=$D2),$F:$F)

Adjust the 40000 to fit your actual last row number.

It will be slow, but it should work.

Sorry, but it is still doesn't work.
Thanks for trying.

If it is possible, can you try to put the formula in Excel, and upload here?

Anyway, I attached 2 spreadsheet, (v3-1) before, and (v3-2) expected results.
The best is, they are both on separated worksheet.
#1 sheet collect all the information on #2 sheet.
Any repeated information on #2 sheet, will only display on #1 sheet once.

[Coconutwei]

bumped for help.
or maybe what I'm asking is too complicated?

I am going to elaborate my questions

Product - Date - Qty
A Jan 3
B Jan 2
C Jan 1
A Feb 2
B Feb 4
A Mar 5
A Mar 4

My result should auto display

Product - Jan - Feb - Mar
A 3 2 9 <-- order twice on Mar
B 2 4
C 1

[Yogi52o]

Something like this?

[Coconutwei]

Something like this?

Not something, it's EXACTLY!
I am trying to go through your formula now, it is a little complicated for me.

Now I am just wondering how to make it looks better, or display in a more user friendly method.
Do you think it's possible, if I create another worksheet, and the result will only be display there once? (Instead of, 1 row with data, others with no data, yet I can't delete them)

p/s : You saved my 3 weeks time to organize the 40k+ of datas. I would gladly donate some buck for your efforts.

[Yogi52o]

Two things:

1) The formula has two main components: the sumproduct, which is multiplying the 1's and 0's coming out of two different equal statements (the first is does the month&year of the column match the month&year of the header row, the second is does the 'Name' column match the 'Name' of the particular row). The second is actually the very first IF clause, which is saying return a blank ("") if this row's Name equals the one above; this ensures it will only print out once for each 'Name'
2) For clean-up, I might create another column at the end of this--either temporary or not--that sums up all the columns, then you could sort/filter on that total column. Only copy things over to a new sheet that have more than 0. Note you would copy/paste values to lock down the numbers. You could also create a new tab with just one unique row per 'Name', and once you understand the formula a bit more, you can alter it to do the lookup on that new unique sheet; plenty of choices for clean-up!

p.s. Glad to help. Thank you for the rep!

[Coconutwei]

Two things:

1) The formula has two main components: the sumproduct, which is multiplying the 1's and 0's coming out of two different equal statements (the first is does the month&year of the column match the month&year of the header row, the second is does the 'Name' column match the 'Name' of the particular row). The second is actually the very first IF clause, which is saying return a blank ("") if this row's Name equals the one above; this ensures it will only print out once for each 'Name'
2) For clean-up, I might create another column at the end of this--either temporary or not--that sums up all the columns, then you could sort/filter on that total column. Only copy things over to a new sheet that have more than 0. Note you would copy/paste values to lock down the numbers. You could also create a new tab with just one unique row per 'Name', and once you understand the formula a bit more, you can alter it to do the lookup on that new unique sheet; plenty of choices for clean-up!

p.s. Glad to help. Thank you for the rep!

Dear Yogi52o,

I experienced with newer problem.
I have to set in one more condition, multiple clients instead of ONLY ONE client.

The formula provided previously, works perfectly if it's only for ONE client.
However, when I tend to add more clients, it seems like the formula abstract ALL clients purchases (instead of their own).

Any suggestion? I highly appreciate any help available.
Attached with spreadsheet for further elaboration.

[Coconutwei]

bump after a month? :P

[Punnam]

Hi Coconutwei ,

I am not sure i will try to help you ,
Information needed
he formula provided previously, works perfectly if it's only for ONE client.
However, when I tend to add more clients, it seems like the formula abstract ALL clients purchases (instead of their own).


Brief me on the about this sentence exactly "when I tend to add more clients, it seems like the formula abstract ALL clients purchases (instead of their own)."

Punnam