Subtotal Count Of Help

**swoosh1014** · 10-13-2014, 03:51 PM

Hello,

I am trying to get a formula working but have been having difficulities producing the desired results. I am wanting a subtotal formula (so that I can filter) that will give me a # value when Column I has a value of "Win" and Column O has an amount greater than 0. I would also like to have this formula ignore a result when Column O is blank. My data range starts in Row 12 and I plan on making it a dynamic range due to future entries. Any help would be greatly appreciated!

To help better illustrate what I am looking for:

Column I Column O

Win 5
Loss -12
Win 7
Win 8
Win
Loss -1

In this example, I would want my formula to exclude the Win with a blank value in column O, resulting in a desired total of "3" .

**sati** · 10-13-2014, 03:58 PM

in cell I10, enter the below formula.

=SUBTOTAL(9,$O$12:$O$10000).

so, when you filter Column I with Win or Loss cell I10 results sum of winning of losing numbers in Column O.

**alansidman** · 10-13-2014, 04:08 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

**ChemistB** · 10-13-2014, 04:09 PM

Until later

**swoosh1014** · 10-13-2014, 04:16 PM

Thanks for the reply! I think I may have explained this a bit poorly, I am wanting a subtotal of the total count of results that match these criteria. So in the example I listed above, there are 3 results that qualify, and I woud want my formula to result in a "3".

**Speshul** · 10-13-2014, 04:20 PM

Try something like

=COUNTIFS(I1:A100,"w",O1:O100,">"&0)

Or

=COUNTIFS(A6:A12,"w",B6:B12,"<>"&"")

**swoosh1014** · 10-13-2014, 04:29 PM

Thanks for the reply!

I entered this formula based on my current range, and received a Value error...

=COUNTIFS(I12:I121,"W",O12:O1221,"<>"&"")

Any suggestions? Sorry I am still an Excel newbie... Will a COUNTIFS formula work with multiple filters or would I need a Subtotal formula in order to do that?

**swoosh1014** · 10-13-2014, 04:32 PM

Figured out why I am getting the value error, however when I filter, my result is not changing like I need.

**Jonmo1** · 10-13-2014, 04:48 PM

Yep, countifs (and most all other functions) don't care if the column has been filtered or not.
They read all the cells regardless if they are visible or not.

The exception is Subtotal (and aggregate in XL2010+)

I'd suggest a helper column in say Z
In Z12 and filled down put

=SUBTOTAL(103,O12)
Filled down to the end of the data

Now you can update your countifs to
=COUNTIFS(I12:I1221,"W",O12:O1221,"<>"&"",Z12:Z1221,1)

**swoosh1014** · 10-13-2014, 05:11 PM

Thanks Jonmo!

I created the helper column and then I entered the following and received an error

=COUNTIFS(I12:I121,"Win",O12:O121,"<>"&"AF12:AF121,1)

It is highlighting the ""AF121 as well as after the last ) .

**Jonmo1** · 10-13-2014, 05:14 PM

You're missing a quote and comma

=COUNTIFS(I12:I121,"Win",O12:O121,"<>"&"",AF12:AF121,1)

**Jonmo1** · 10-13-2014, 05:23 PM

Actually, we don't need the test in countifs for column O <> ""
Because now subtotal is doing it. (Assuming the subtotal you put in column AF is referring to column O)

So you can just have
=COUNTIFS(I12:I121,"Win",AF12:AF121,1)

**swoosh1014** · 10-13-2014, 05:27 PM

I sure was... Thank you so much!!!! This has been driving me crazy for days now and it looks like this will work.

One other question if you dont mind... currently my data range ends at 121, however I plan on adding on to this for many years into the future. If I copy my helper column all the way down, I am no longer able to insert new rows between my the current last row of data (Row 121) and Subtotal formulas I have built in on Rows 122/123. Is there a way that when I add a row to enter new data, that the helper column I set up can automatically have that formula already entered in the new row, helper column cell so that I dont have to keep copying it in manually every time a new row is added? (Hope that made sense

)

**Jonmo1** · 10-13-2014, 05:29 PM

Take advantage of tables.

Highlight the ENTIRE dataset
Click Insert - Table

Now anytime data is added to a column adjescent to the column with the formula, that formula will be auto filled down.

Subtotal Count Of Help

LinkBack

Thread Tools

Rate This Thread

Display

Subtotal Count Of Help

Re: Formula Help

Re: Formula Help

Re: Formula Help

Re: Formula Help

Re: Count of Subtotal Help

Re: Count of Subtotal Help

Subtotal Count Of Help

Re: Subtotal Count Of Help

Re: Subtotal Count Of Help

Re: Subtotal Count Of Help

Re: Subtotal Count Of Help

Re: Subtotal Count Of Help

Re: Subtotal Count Of Help

Thread Information

Users Browsing this Thread

Similar Threads

Copy Value String text of Formula become Formula but the formula not automatically active

how to hide formula in formula box, view lookup result in formula box?

Bookmarks

Bookmarks

Posting Permissions