I have a spreadsheet that has the following wonderful SUMPRODUCT formula that only calculates based on visible cells which I would like to achieve with the same effect by using the new SUMIFS with SUBTOTAL functions.
=SUMPRODUCT(--ISTEXT(G4:G34),--(J4:J34>100),SUBTOTAL(103,OFFSET(G4:G34,ROW(G4:G34)-MIN(ROW(G4:G34)),,1))*(P4:P34))
I would prefer to use SUMIFS as it delivers the results much faster. Any help is much appreciated.
Last edited by e_lad; 09-15-2011 at 12:20 PM.
You can't use expressions in SUMIF/SUMIFS, such as ISTEXT(rng), so you are stuck with SUMPRODUCT.
What about using those wildcard alternative such as...
=SUMIFS(P4:P34,G4:G34,"=*",J4:J34,">100",...
How would you implement the SUBTOTAL function into the above formula to enable it to only calculates based on filtered visible cells?
Last edited by e_lad; 09-15-2011 at 01:37 PM.
Can anyone help on this?
Bob's right, you can't use SUMIFS unless you are prepared to add a helper column, e.g. in Z4 copied down useOriginally Posted by Bob Phillips
=SUBTOTAL(102,P4)
then you can use SUMIFS based on that column as well as your other criteria
=SUMIFS(P4:P34,G4:G34,"?*",J4:J34,">100",Z4:Z34,1)
Last edited by daddylonglegs; 09-15-2011 at 04:52 PM.
Audere est facere
That's a shame...I was really hoping that I could introduce SUBTOTAL within SUMIFS just like SUMPRODUCT.
I don't think adding another column for SUBTOTAL will be neat so it looks like I will have to resort to SUMPRODUCT again. The only problem with this is...it can get really slow as I intend to have quite a number of these formulas with large amount of data
Thanks Daddylonglegs
Then use helper columns as the insect suggested, you can always hide those columns. SUMPRODUCT should be avoided where performance becomes an issue.
I'll give it a go anyway so see what it looks like. Thanks for the help.
For once, I wasn't quite sure what insect you were on about...then I realised when I scrolled back up to see Daddylonglegs's staring at me :-)
I couldn't quite bring myself to spell it out
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks