Solved

SUMIF over criterias for column and rows

Posted on 2014-07-21
10
176 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 50

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 50

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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem to line 23 54
MS Excel Multi Sheet Formula 13 34
Excel Formula for Commission Structure 2 20
Excel Sheet Data Finding 14 18
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

829 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