Question description:
This user has given permission to use the problem statement for this
blog.
Hello, I need help trying to create a sum of numerical values on a spreadsheet in which the only values that apply to the sum are when the status of another cell is a certain value (e.g. value is not in sum when status: closed, value is in sum when status: open)
Solved by T. W. in 18 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
12/09/2018 - 08:55
Hello?
Excelchat Expert
12/09/2018 - 08:55
Welcome, Thanks for choosing Got It Pro-Excel.
Excelchat Expert
12/09/2018 - 08:55
Do you have sample data that we can work with?
User
12/09/2018 - 08:56
No I don't im afraid. I only have my real data which is confidential
User
12/09/2018 - 08:56
I can quickly strum up an example
Excelchat Expert
12/09/2018 - 08:56
Very good.
Excelchat Expert
12/09/2018 - 08:57
Let me know once you are done.
User
12/09/2018 - 08:57
okay im done
User
12/09/2018 - 08:58
essentially it is a large sheet and the Levels (1, 2, 3) may change on sheet1 and therefore I need sheet2 to be responsive to that?
Excelchat Expert
12/09/2018 - 08:59
Okay.
Excelchat Expert
12/09/2018 - 08:59
There is a couple of ways this can be achieved.
Excelchat Expert
12/09/2018 - 08:59
We can use:
Excelchat Expert
12/09/2018 - 09:00
SUMIF function
Excelchat Expert
12/09/2018 - 09:00
DSUM Database Function
Excelchat Expert
12/09/2018 - 09:00
Or pivotTables
Excelchat Expert
12/09/2018 - 09:00
Let's use the SUMIF function.
User
12/09/2018 - 09:00
okay, thats interesting. I'm more familiar with SUMIF functions so lets go with that
Excelchat Expert
12/09/2018 - 09:05
Thank you for your patience.
Excelchat Expert
12/09/2018 - 09:05
For the simple data, we can use the function:
Excelchat Expert
12/09/2018 - 09:05
=SUMIF(Sheet1!$A$2:$A$7,A1,Sheet1!$B$2:$B$7)
Excelchat Expert
12/09/2018 - 09:05
That is in cell A2 of Sheet2
Excelchat Expert
12/09/2018 - 09:06
It is automatic, once data in sheet1 changes, it is reflected on sheet2
User
12/09/2018 - 09:06
that is lovely
Excelchat Expert
12/09/2018 - 09:06
Is there any questions or clarifications you may need in regards to this?
Excelchat Expert
12/09/2018 - 09:07
Note:
User
12/09/2018 - 09:07
yeah its exactly what I want so THANK YOU. but how did the formula pick up that I wanted level 1 to and 3 from sheet1?
User
12/09/2018 - 09:07
1, 2 and 3*]
Excelchat Expert
12/09/2018 - 09:08
In the formula we have this:
Excelchat Expert
12/09/2018 - 09:08
Is there any questions or clarifications you may need in regards to this?
Excelchat Expert
12/09/2018 - 09:08
Sorry
User
12/09/2018 - 09:08
haha its okay
Excelchat Expert
12/09/2018 - 09:08
=SUMIF(Sheet1!$A$2:$A$7,A1,Sheet1!$B$2:$B$7)
Excelchat Expert
12/09/2018 - 09:08
Do you see the reference A1
User
12/09/2018 - 09:08
oh yes!!!
Excelchat Expert
12/09/2018 - 09:09
A1 is the criteria
User
12/09/2018 - 09:09
it picks up where it states that word in the first $A:$A column
Excelchat Expert
12/09/2018 - 09:09
EXACTLY
Excelchat Expert
12/09/2018 - 09:09
You can as well replace the reference A1 with the text "Level 1"
Excelchat Expert
12/09/2018 - 09:10
It can work equally the same.
Excelchat Expert
12/09/2018 - 09:10
Similarly to C2
Excelchat Expert
12/09/2018 - 09:10
=SUMIF(Sheet1!$A$2:$A$7,C1,Sheet1!$B$2:$B$7)
Excelchat Expert
12/09/2018 - 09:10
We have C1 in the formula.
User
12/09/2018 - 09:10
does this work if I select the entire row instead of a highlighted range?
Excelchat Expert
12/09/2018 - 09:10
C1 represents "Level 3"
Excelchat Expert
12/09/2018 - 09:11
Yeah it works.
Excelchat Expert
12/09/2018 - 09:11
But note:
Excelchat Expert
12/09/2018 - 09:11
The first range must be equal in size with the second sum_rangel(the range to be summed)
User
12/09/2018 - 09:12
okay
Excelchat Expert
12/09/2018 - 09:12
Picking the whole column consumes memory, hence makes calculations in Excel to be sloe.
Excelchat Expert
12/09/2018 - 09:12
*slow
User
12/09/2018 - 09:12
sure
Excelchat Expert
12/09/2018 - 09:13
But if you don't have a problem with that, you can just pick the whole columns.
Excelchat Expert
12/09/2018 - 09:13
Anything else?
User
12/09/2018 - 09:13
I think it should be okay thank you for your time :)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
