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 NewITZone Your Own Question
NewITZone
NewITZone, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 90
Experience:  I have been using Office Pro for 10 years and implemented applications for business use.
53509759
Type Your Microsoft Office Question Here...
NewITZone is online now
A new question is answered every 9 seconds

Here is the model Source Table: Column A (Name) Column

Customer Question

Here is the model
Source Table:
Column A (Name) Column B (Score)
Gil Gibori 5
Carrie Gibori 3
Gil Gibori 8
Value table:
Column A (Name) Column B (Sum)
Gil Gibori 13
Carrie Gibori 3
Formula: = SUMIFS('Improvement =1/1/2010", $Date, "<=12/31/2016”)
I am trying to model the sum of several scores for over 30 people from a spreadsheet with several hundred data points. So far, I have been editing each formula manually, replacing the people's names by typing them in. When I try to insert the cell in the first column, containing their name, it incorporates the identifier of the cell (i.e. A19) and not the text. This leads to a false value.
Example: = SUMIFS('Improvement =1/1/2010", $Date, "<=12/31/2016”)
Value output: 0
Is there a way to have the formula draw out the text in the Name column to include in the formula?
I hope this makes sense. I am baffled.
Thanks.
Submitted: 8 months ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 8 months ago.

please send example file

Customer: replied 8 months ago.
Attached is a sample.
Expert:  The-PC-Guy replied 8 months ago.

still unclear of what you are trying to do you want to get an average of what exactyl?

Customer: replied 8 months ago.
The AverageIFs is not the issue because I am having the same issue with SUMIFS as well. The formula in sheet 2 is set up to calculate the average of the score improvements from sheet 1. In this example I have only two names. In my actual sheet, I have nearly 100.Rather than having to change each formula manually by typing in the name of the person in column A, I'd like to be able to simply insert the column A cell "text" into my formula and copy it to each row.NOW:: =AVERAGEIFS ('ACT Diagnostic Data - Table 2'!L2:L12, 'ACT Diagnostic Data - Table 2'!$J2:$J12, "Giancarlo DiPierro" ....WANT:: =AVERAGEIFS ('ACT Diagnostic Data - Table 2'!L2:L12, 'ACT Diagnostic Data - Table 2'!$J2:$J12, "{TEXT FROM A2}" ....
Expert:  The-PC-Guy replied 8 months ago.

ok, still not following, which particular columns do you want to sum on which sheet, what criteria do you want to use, and where is that criteria comming from, and which cell on which sheet should the result go in.

Customer: replied 8 months ago.
This is really not a formula issue. It is a "how to do it in Excel" question.Let's simplify the formulas:Source Table: (Sheet 1)
Column A (Name) Column B (Score)
Giancarlo DiPierro 5
Gil Gibori 8
Giancarlo DiPierro 1
Gil Gibori 6=AVERAGEIFS ('ACT Diagnostic Data - Table 2'!L2:L12, 'ACT Diagnostic Data - Table 2'!$J2:$J12, "Giancarlo DiPierro" ....
=AVERAGEIFS ('ACT Diagnostic Data - Table 2'!L2:L12, 'ACT Diagnostic Data - Table 2'!$J2:$J12, "Gil Gibori" ....Value Table (Sheet 2):
Giancarlo DiPierro 3
Gil Gibori 7How do I build a formula that does not require me to manually retype the name in each row. i.e. Giancarlo, Gil?
Expert:  The-PC-Guy replied 8 months ago.

sorry it is unclear to me as to exactly what you want to accomplish, so for that reason I will open your question to other experts

Expert:  Bhavik Joshi replied 8 months ago.

Hi ,

I will try to help you in it.

Expert:  Bhavik Joshi replied 8 months ago.

I got your Question. I will start working on it.

Customer: replied 8 months ago.
Thanks.
Expert:  Bhavik Joshi replied 8 months ago.

Can you please give me complete sheet as you have use column L in your formula but in the sheet you gave me you have given data in that column

Expert:  Bhavik Joshi replied 8 months ago.

try using &A2 in place of "Giancarlo DiPierro" . It should work

Expert:  Bhavik Joshi replied 8 months ago.

Did you try above suggestion ? Did this work for you ?

Related Microsoft Office Questions