Need help with a sum formula in Excel

[sarahjane79]

Ok, I know I have done this before but for some reason I am having a terrible time with it now...Please help & thanks in advance.

I have this table:

Inititive |Cost |Approved? | Notes
PR Team |5,000 |X |
Print Plan |2,500 | |See Jamie
Web Development |10,000 |X |
Prizing |2,000 |X |

It goes on for about 100 rows. The Cost column is fed by seperate pages in the workbook.

What I want to do is have a Total Approved Budget box that adds only the rows with an X in the Approved column.

Can anyone help? Thanks in advance.
Sarah

 

[verizonrep]

I use sumif for this one. Assuming the following columns...

A=Initiative B=Cost C=Approved

And the data is between A2 and C101 (you said there were 100. Here's the formula that you would put in an easy to view cell (like B102):

=Sumif(C2:C101,"x",B2:B101)

 

[SkipVought]

Hi,

Check out Dynamic Named Ranges...

How can I rename a table as it changes size faq68-1331

How does this formula look to you?
[tt]
=Sumif(Approved,"x",Cost)
[/tt]


Skip,

_[red][/red]

 

[verizonrep]

To Skip's point, if you want to make a range that adjusts to the table's length, you will need to set up two ranges. Per my previous example, if column B is cost and column C is Approved, AND lets say the worksheet was named "Data" then you would do the following:

Insert>Name>Define

in the "Names in Workbook" field, type "Approved" and under "Refers to" write the following equation:

=Offset(Data!$C$1,0,0,counta(Data!$A:$A),Counta(Data!$1:$1))

we are focusing on column A only because it is a field that will not contain blanks.

Next, do the same for cost:

Insert>Name>Define

in the "Names in Workbook" field, type "Cost" and under "Refers to" write the following equation:

=Offset(Data!$B$1,0,0,counta(Data!$A:$A),Counta(Data!$1:$1))


Now, you are ready for the equation that skip gave you.

=Sumif(Approved,"x",Cost)

I would suggest that you put the equation in a field like E1 (right after your notes column header). This way, you won't have to scroll all the way to the bottom, or have to move your equation field out of the way of new data.

 

[SkipVought]

...and there's yet another option.

AutoFilter rows having some criteria

Then use the SUBTOTAL function, 9 for argument 1 and the COST RANGE, for the SUM.

Will sum VISIBLE cells only.


Skip,

_[red][/red]

 

[sarahjane79]

Thank you all so much -

Verizonrep - your formula worked perfectly and then I started applying it to underlying worksheets as well and they were all different lengths and changing the length was annoying on each sheet!

So I was about to post to see if there was a way to do it dynamically and there was SkipVought's response.

You guys are great, I hope I can pass on information that helps someone as much!