Hi-
Can you take a look at attached. In front tab commissions, my sumproduct formula is not working. It's grabbing data from eze data tab. The Year to date works, but when i want to further break it down to q1, it doesn't work. I have only attached a sample of my data.
Thankssumproduct questioin.xlsx
Your formula for Q1 is:
=SUMPRODUCT(('EZE DATA'!$M$2:$M$1962)*('EZE DATA'!$D$2:$D$1962="ACTNS")*('EZE DATA'!$E$2:$E$1962="LatAm")*('EZE DATA'!C2:CC22000="Q1"))
Looking at the bit for Q1:
'EZE DATA'!C2:CC22000
I'd say you have a typo and this should be
'EZE DATA'!$C$2:$C$1962
When using sumproduct I'd suggest it's normally a good idea to keep the arrays the same size...
when I made it the same size it worked. That's strange that you have to do that. Thanks
With .xlsx files you can use the much more efficient SUMIFS instead...
=SUMIFS('EZE DATA'!$M$2:$M$1962,'EZE DATA'!$D$2:$D$1962,"ACTNS",'EZE DATA'!$E$2:$E$1962,"LatAm",'EZE DATA'!$C$2:$C$1962,"Q1")
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Or with an pivot table.
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
I like the look of a pivot table better. Since I use the eze data tab daily to import daily info, is there a way for the pivot table to update automatically daily? I tested it to add more data, but it's not pulling in the numbers.
nevermind, just have to click refresh. If you were to create a macro, is there a way to build that in to refresh the pivot table?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks