SUMIF over criterias for column and rows

Hi Experts,

I have a table with, for example, names of sales staff in Column A.  Column B, C, D.... represent calendar days and the sales that each staff made on a given calendar day.

I'd like to write a formula on a separate worksheet that calculates the sales when the user enters:
Input Variables
B1 (Sales Rep Name): Jane Doe
B2 (Start Date):  01/01/2014
B3 (End Date):  01/15/2014

Output on cell C1 should be Jane Doe's sales from 01/01/2014 - 01/15/2014

Thanks
rav_ravAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

duncanb7Commented:
at C1 cell , you try, and those data is from B1, B2, B3

=TRIM(MID(B1,FIND(":",B1)+1,100))&"'s sales from "&TRIM(MID(B2,FIND(":",B2)+1,100))& " - " &TRIM(MID(B3,FIND(":",B3)+1,100))

Output on cell C1 is  Jane Doe's sales from 01/01/2014 - 01/15/2014

Duncan
0
Rgonzo1971Commented:
Hi,

pls try

=SUMIFS(Sheet1!D:D,Sheet1!A:A;B1,Sheet1!B:B,">="&B2;Sheet1!C:C,"<="&B3)

Regards
EE20140722.xlsx
0
Rgonzo1971Commented:
And if you do not have SUMIFS

pls try

=SUM((Sheet1!D2:D1000)*(Sheet1!A2:A1000=B1)*(Sheet1!B2:B1000>=B2)*(Sheet1!C2:C1000<=B3))

as an array Formula Enter with Ctrl-Shift-Enter

Regards
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Glenn RayExcel VBA DeveloperCommented:
You can accomplish this with a SUMIFS function, assuming you're using Excel 2010/2013 (based on your previous questions).

I've created an example file that demonstrates this.  The sales data is in a table on Sheet1; the data entry cells and resultant SUMIFS function are on Sheet2.  The SUMIFS function is as follows:
=SUMIFS(Sheet1!D:D,Sheet1!A:A,B1,Sheet1!B:B,">="&B2,Sheet1!C:C,"<="& B3)

One thing that isn't clear from your description - except for the salesperson name in column A - is exactly what each column in your sales data represents.  I assumed:
A - Salesperson name
B - Sales Date
C - some other date (not taken into account in this example)
D - Sales Amount

Please clarify if the additional date (if it exists) is indeed relevant and how it needs to be measured.

Regards,
-Glenn
EE-Q-28480665.xlsx
0
rav_ravAuthor Commented:
Hi All,
Sorry for the confusion.  I've modified Glenn Ray's file to show how my table actually looks like.  I have dates sequential dates in the column headers and the sales amounts in the table cells.  In real life, I have about 120 calendar days currently, so 120 columns.

How would the SUMIF / SUMIFS formulas be changed to give the desired results?

Many Thanks
EE-Q-28480665-v2.xlsx
0
Glenn RayExcel VBA DeveloperCommented:
You can use up to 127 criteria in a SUMIFS statement, but you wouldn't be able to use it with the table layout you described since the date information isn't included in each row of data.  This is just one reason why this is not a good data table layout.  Transactional data like this should be timestamped; this layout allows for a lot of null values (ex., dates where no sales occur for a given individual).

Will probably need a complex array formula to resolve this, unless you're willing to transpose your original data.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
On the Raw Data tab, are you averse to using a helper column? If not, in a spare column use the following formula:

=IF($A2=Output!$B$1,SUMIFS($B2:$E2,$B$1:$E$1,">="&Output!$B$2,$B$1:$E$1,"<="&Output!$B$3),0)

This looks at the name in column A and compares it to the Output tab name. If not the same it gives zero otherwise it adds up the row for the dates in the range on the Output Tab.

I have put this in cell H2 but as you say you could have up to 120 columns it would be much further to the right; adjust the ranges B:E to accomodate the required column.

Your formula on the Output tab can now just be a sum of the helper column, ie =SUM('Raw Data'!H:H)

At the moment this is giving zero value for me but the issue is because the date headers in the Raw Data are text rather than true dates.

Thanks
Rob H
0
Rob HensonFinance AnalystCommented:
Hmm, looks like a Table cannot have True Dates as headers, they keep getting converted to text even when overwritten.

If you convert the Table to a Data Range, and convert dates to true dates, the above works.

Thanks
Rob H
0
rav_ravAuthor Commented:
Hi Glenn, Rob,
Thanks for your help.  
Glenn:  Out of curiousity, what would the complex array formula look like?
Ravi
0
Glenn RayExcel VBA DeveloperCommented:
Rav...I was still trying to work that out.  I don't think our comments merit a solution just yet.

I was able to partially solve the problem using an array function like so:
=SUMPRODUCT(--(COLUMN('Raw Data'!B1:E1)>=$C$2)*--(COLUMN('Raw Data'!B1:E1)<=$C$3),{1770,6000,1770,2401})

This is not an array function, but it does contain an array of values at the end that represent all sales for Jane Doe for all dates listed.  The formula properly gets only those values for the corresponding start and end dates selected.  However, I have not been able to figure out how to add a function to create all possible total values for any individual and replace the array in there now.  That's where I'm stuck.

-Glenn
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.