Excel - SUM Function Problem - Expert Solution

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

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.