Solved

SUMIF over criterias for column and rows

Posted on 2014-07-21
10
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 51

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 51

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 33

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 33

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

705 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