How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1844
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

Looking for help on a SUMIFS scenario with variability in

Customer Question

hi, looking for help on a SUMIFS scenario with variability in the sum data based on column headers
Submitted: 4 months ago.
Category: Microsoft Office
Expert:  Jess M. replied 4 months ago.

Hi, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.

Can you please send (or attach on your reply) a sample Excel file showing what you want to accomplish?

Please let me know by replying to me here so that I can help you further.

Best regards,
Jess

Customer: replied 4 months ago.
Hi jess,- attached is a very small cut off of a very large data set I have
- I have spent an hour on this trying to do a quick formula and have no been able to do it. Im very frustrated and am not looking at this clearly.
- yes, there are manual multi-step ways around creating additional tables with date mapping to allow sumifs to work, but given size of data i would prefer not. looking for efficient way. Again, remembering the data set I have is 100x larger than thisGoal:
- Populate the yellow cells using as much automated formulas as possible.Challange is that the retrieve columns need to be dynamic for each person because they all start on different datesAny ideas?
Customer: replied 4 months ago.
My best theory thus far is a combination SUMIFS / Index / Match - but I can't seem to put it together
Expert:  Jess M. replied 4 months ago.

Thank you for the sample file. Regarding this sample data, is it only UNTIL column Q but from rows 3 to 100000? Or are there other data past column Q?

Customer: replied 4 months ago.
The real file goes well past column Q. to about column AS or so.. I I only provided this smaller dataset as an example. Im attaching the true file. Again, the goal is just finding a way to have the SUMIFS be dynamic based on column header
Expert:  Jess M. replied 4 months ago.

Thank you for the sample file. How "dynamic" is your column header? In the first sample you gave you with the yellow columns, the headers are Months 0-3, Months 4-6, what are they representing? Your actual data are Month-Year like Jan-15, Feb-15 which means January 2015 and February 2015 respectively.

Customer: replied 4 months ago.
Hi JessLet me step back any give you a high level goals again
- The data has employee start date, and then monthly periods of bookings
- Im trying to figure out on average how much the (on average) a sales person does in bookings during their first 12 quarters with the companyTherefore, Im trying to write a formula that considers their start date, and also the date in the column heading to report out the results of their first 12 quarters (36 months) of employment.
Expert:  Jess M. replied 4 months ago.

Let us clarify few things first on your actual data, let us take row 5 (Rep_1). His start date is 2009 but the actual data starts 2013. So the valid columns will be J (first month) through AS (36 month).

For row 7 (rep_5), the start date is 2015 but the actual data is 2013. So valid columns will be AG (first month, March 2015) and AS as the 13th month?

Are these observations correct?

Customer: replied 4 months ago.
these are correct observations. I can write my own qualifying formulas that limit out examples like you gave from the final averages
Customer: replied 4 months ago.
I would as you just pick an example that is 'normal' and use that to write the formula
Expert:  Jess M. replied 4 months ago.

In the first sample you gave me, your HEADERS are Months 0-3, Months 4-6, etc. So you are grouping them in quarters. So you need to go until Months 34-36?

Expert:  Jess M. replied 4 months ago.

Or are you suppose to say Quarter 0-3, Quarter 4-6, ... Quarter 10-12?

Customer: replied 4 months ago.
Jess,It really doesn't matter, I just need the foundation for the formula and I can tweak after that....You can build it with months or quarters and I can adjustif you have questions - please call my cell at(###) ###-####
Expert:  The-PC-Guy replied 4 months ago.

here you go, since each rep has their own unique row this is simplified. A simple sumifs will work then. See column AT for the formula

Expert:  The-PC-Guy replied 4 months ago.

---------------------------------------------------------------------------------------------------------------

let me know if you have any questions, problems, or concerns

PLEASE DON'T FORGET TO RATE SO I AM PAID FOR MY TIME

IT WILL NOT COST ANYTHING ADDITIONAL BEYOND THE VALUE OF YOUR QUESTION

TO RATE, CLICK THE STARS AT THE TOP OF YOUR SCREEN

Do not rate negatively, instead continue the conversation with me so I can address any of your concerns

---------------------------------------------------------------------------------------------------------------

if you have any questions in the future you can visit my profile

http://www.justanswer.com/computer/expert-1expert-2rulethemall/

and ask a question right in my box,

also you can put "PC Guy only" in your question title if you want to get to me.

Customer: replied 4 months ago.
Hi PC Guy,Please check your work here: if I look at row 6, for example, it totals 398k... which is the sum of the entire row..To repeat the requirement again: There needs to be a delineation between employee start date and the date shown in row 4. Your formula as written is simply summing the entire row
Expert:  The-PC-Guy replied 4 months ago.

do the entire row of dates fall within 3 years of the starting date, which was your requirement, then yes it would sum the entire row

Expert:  The-PC-Guy replied 4 months ago.

trust me the formula is working as requested, double check your data remember it is starting dynamically based on the start month and year, and then adding for upto 3 years from that date according to your requirement. This is all based on start date and the dates across the top of your columns.

Expert:  The-PC-Guy replied 4 months ago.

if you see any other problems let me know