Solved

SUMIF over criterias for column and rows

Posted on 2014-07-21
10
174 Views
Last Modified: 2014-07-27
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
0
Comment
Question by:rav_rav
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 13

Expert Comment

by:duncanb7
ID: 40210258
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
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40210787
Hi,

pls try

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

Regards
EE20140722.xlsx
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 40210795
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40210801
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
 

Author Comment

by:rav_rav
ID: 40211430
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 27

Accepted Solution

by:
Glenn Ray earned 250 total points
ID: 40212174
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40213924
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
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 40213962
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
 

Author Closing Comment

by:rav_rav
ID: 40223052
Hi Glenn, Rob,
Thanks for your help.  
Glenn:  Out of curiousity, what would the complex array formula look like?
Ravi
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40223450
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now