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