Hello:
Please refer to attached file.
I have Invoice Data for each store in sheet1.
Column A is store#, Column C is Inv#, Column D is Date, Column E is Item# and Column P is Cost per item.
I need formulas in Cell T3:V50 to consolidate the each unique invoice total for each store listed in Cell U1 and V1.
Let me know if you have any questions.
Thanks.
In T3, copied down...
=IFERROR(INDEX($D$2:$D$26,MATCH(0,INDEX(COUNTIF($T$2:T2,$D$2:$D$26),),0)),"")
in U3, copied down and across...
=IF(T3="","",SUMIFS($P$2:$P$26,$A$2:$A$26,U$1,$D$2:$D$26,$T3))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi there. see the attached file.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
I was busy attaching my answer while Ford got in there first.... again... However, i did return the unique date list by a different method... Ford's is easier than mine, though...
Hi Glenn and Ford:
Thank you so much for a great help.
Love the detailed in the formula.
I would like to add Invoice # in column W if possible.
Please let me know if you have any questions.
Please let me know if you have any questions.
Thanks.
Riz
If there is more than one invoice number on a day (can this happen?? If so, how do you want this handled), this won't work. If there's only one, this will do it:
=iferror(INDEX($C$2:$C$26,MATCH(T3,$D$2:$D$26,0)),"") in W3 & drag down...
Hi Guys:
Glenn has valid point.
To avoid the problem with one invoice on a single day, we probably need to separate out each store.
Please refer to attached sheet column T to Z where we need formulas.
Please let me know if you have any questions.
Thanks.
Riz
Hello:
forgot to attach file. I have now attached.
Sorry
Riz
OK... Try this version out.
Hi Glenn:
Thanks a lot, this will work great for me.
Riz
You're welcome... any time!
Hello Glenn:
Sorry to bother you.
Please refer to attached file.
I have used your solution and seems to work with my large data file as attached.
However, i am seeing some weird result for single date for Store# 43227 as you can see in Column AB:AD.
I would really appreciate your help me point where the error is.
Let me know if you have any questions.
Thanks.
Riz
Hi. Try this. The absolute references weren't the best to make copying/pasting easy. Should be OK now. If not, shout. I'll be back in UK morning. Off to a wind- and rain-swept BBQ now!!
Hi Glenn:
You are my men...works great
Thanks
Riz
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks