Sum only Visible

[mcrae406]

I have a table that has headers such as contractors, distributors, job name, total dollar value, won/not won, etc. I have a sumif function on the side that will add all the totals if we have won the job and add all the totals we have not won. But if i want to use the table drop down to look at all of the jobs with one particular distributor the sumif cells on the side disappear. what i am wanting is that same formula to work on for only the visible cells so that when i sort based on my many different criteria's the running totals for jobs won/not won always update. Also i have to use this file as part of a presentation using the only sheets program. When i use this program i have noticed that i cannot use the drop downs from my table. Is there a way to fix that? do i need to save it in a different format?

[CK76]

Without seeing your data. Hard to give you exact solution.

But you could use combination of SUMPRODUCT with Subtotal and Offset.

See example in link below.
https://www.mrexcel.com/forum/excel-...ells-only.html

[queuesef]

Research DSUM in excel help or internet!

[CK76]

Or use PivotTable to summarize

[mcrae406]

hopefully this link will work so that you can see what i have at this point. As i said my hope is that i can sort using the drop downs and have the "not awarded" and "won" update automatically

https://docs.google.com/a/ssco.net/s...it?usp=sharing

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting). Also your external link did not work.

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[CK76]

I'd suggest you upload it here. Most of us are reluctant to open external file.

If you must, at least make the file public/open to those that access it via link.

[mcrae406]

Sorry i didnt see the manage attachments section first. I have attached a basic example of what i am looking for. Looking at the attachment this is what i would like: If I select the Dist drop down and sort to B i want the totals in K3 and K7 to update automatically based on the visible cells. In this example the Won section would show $28,979 and the Not Awarded would be 0

[CK76]

Something like this?

Please Login or Register  to view this content.

Or using table reference...

Please Login or Register  to view this content.

See L8 & L9 in attached.

[CK76]

Here's pivot table method with slicer control.

[mcrae406]

Thank you so much! Both of these solutions work perfectly now its just a matter of deciding which one to go with!

On another note, are any of these features (tables included) able to be viewed using google sheets? Its the easiest way for me to show this data during out weekly presentation although not ideal.

[CK76]

Sorry, can't help you with Google sheet. I only use it as data storage and never explored other functionalities.