custom formula within a subtotaled spreadsheet

tink bell

New Member
I had posted a multipart question on another thread & I think it got buried under the original subject title, bc I have not received an answer & am still stuck. here's a message I drafted to a relative, but this was way over his head:
---
what I'm doing is subtotaling (as opposed to pivots which I'm pretty decent at), but need to change the subtotal formulas for each group range. so for example, col F sums the order count (units ordered), but col G *counts* the # of different models ordered. easy enough. I simply find/replace =subtotal(9, with =subtotal(3,.

it start to get tricky when I want to change an entire formula. for example, the subtotal formulas read:

=SUBTOTAL(9,F2:F8)

and

=SUBTOTAL(3,G2:G8)

however, I need cols J &K to be an IF statement.

=IF(COUNTIF(J2:J8,"yes"),"Yes","")

and

=IF(COUNTIF(K2:K8,"Yes"),"Yes","")

the problem I'm running into is that when I collapse the group to level 2 & copy the formula > Go To > Paste Visible, the ranges get out of whack. meaning when I open up the formula (either F2 or double click w/in the cell, the blue range box will often either overlap the group above including extra information (extra yeses yielding false results), or not include the full range (in the above examples of rows 2:8 it may only got to row 6 or 7, excluding 8.

my 2nd *fix* for this is to include cols J & K in my subtotal, & somehow replace the formula w/a custom formula (either by a simple find/replace, or VBA). thoughts?

since I haven't been able to find an easy solution, I'm having to manually adjust each range daily for 1550 rows. extremely time consuming & inefficient. I'm certain there is a way, I'm just unaware. can you help?
---

so, I've spent literally hours googling this & can't find anything that works. I am *not* looking for someone to create the spread sheet for me. I am genuinely interested in learning how to do this for future reference, so i truly appreciate the expertise of the members here on the forum. THANKS!!!!

also, I tried to install the html maker, but couldn't figure out how to get it to work. got an error about 64 bit something or other. I'm sorry, but I'm just not that savvy. also can't even figure out how to include jpgs of screen shots.

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Replies
3
Views
155
Replies
0
Views
144
Replies
2
Views
318
Replies
10
Views
320
Replies
2
Views
259

1,195,666
Messages
6,011,047
Members
441,580
Latest member
BornholmerBjarne

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back