Solved

SUMIF over criterias for column and rows

Posted on 2014-07-21
172 Views
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
Question by:rav_rav
• 3
• 2
• 2
• +2

LVL 13

Expert Comment

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 48

Expert Comment

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 48

Expert Comment

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

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

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

LVL 27

Accepted Solution

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 31

Expert Comment

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 31

Assisted Solution

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

ID: 40223052
Hi Glenn, Rob,
Glenn:  Out of curiousity, what would the complex array formula look like?
Ravi
0

LVL 27

Expert Comment

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

Suggested Solutions

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.