0

The following formula provides me a subtotal of all filtered items in column BB with a dynamic range. =SUBTOTAL(9,BB15:INDEX(BB:BB,MATCH(E1+100,BB:BB))) I would like to add a criteria that provides me a subtotal of all filtered items, as long as column Q also = AJ3. I've tried numerous ways to do this without success. Any suggestions on how I can "get 'er done"?

LoriLee
  • 3
  • 3
  • try something like this: http://stackoverflow.com/questions/35903731/countif-with-filtered-data. You would use the variable range in place of the ranges. Also change the 3-count to 9-sum. – Scott Craner May 03 '17 at 20:47
  • here is another one: http://stackoverflow.com/questions/43746214/subtotal-sumproduct-multiple-criteria-offset-using-a-dynamic-range/43747192#43747192 – Scott Craner May 03 '17 at 20:48
  • And yet another: http://stackoverflow.com/questions/41595676/how-to-combine-subtotal-and-sumif-with-a-range-of-criteria – Scott Craner May 03 '17 at 20:49
  • Basically a quick google search gave a lot of examples: [subtotal with sumif stack overflow](https://www.google.com/search?q=subtotal+with+sumif&rlz=1C1CHBF_enUS715US715&oq=subtotal+with+sumif&aqs=chrome..69i57j0l4.10223j0j7&sourceid=chrome&ie=UTF-8#q=subtotal+with+sumif+stack+overflow) – Scott Craner May 03 '17 at 20:52
  • Hi Scott, Thanks for the links. I've visited each prior to submitting this question. I was told that using the formula I posted is less volatile and will allow faster processing speeds. Which is why I'm trying to figure out how to incorporate multiple criteria into it. I understand there will be a circular reference error, but I've already fixed that. – LoriLee May 03 '17 at 21:22
  • The formula you used contains a variable range `BB15:INDEX(BB:BB,MATCH(E1+100,BB:BB))` which is BB15 to the last cell in column BB with a number. Just replace all the range references in those linked with this one. and a matching variable range formula for the criteria using column Q instead. Try to merge those linked with yours, then post your attempt. – Scott Craner May 03 '17 at 21:40
  • Failed attempts: =SUBTOTAL(9,AU15:INDEX(AU:AU,MATCH(E1+100,Y:Y),AU15:INDEX(AU:AU,MATCH(Q:Q,AJ3)))) ALSO: =SUBTOTAL(9,AU15:INDEX(AU:AU,MATCH(E1+100,Y:Y)*(Q:Q,AJ3))) - I say failed because I receive no value or error message; just a zero even though I know it should return "5.71". – LoriLee May 04 '17 at 14:51
  • Oops, both "Y"s in my attempts should be "AU"s above, but the results are still the same. – LoriLee May 04 '17 at 15:08
  • You need to use the formulas from the links with your variable range formula, SUMPRODUCT and OFFSET must be a part of the formula. – Scott Craner May 04 '17 at 15:10
  • Scott, I did that days ago and it bogged down my system big time. I thought I had just figured it out, but realized the number that is being returned is all of AU and not just those items that match AU. So, what you are saying is that the formula I am working with won't work with multiple criteria? – LoriLee May 04 '17 at 15:44
  • That is correct. Are you always filtering on the same thing. Because if you know the filter you can just use SUMIFS() and include the filter value in the SUMIFS(). But if you the filter is different each time then you will need the SUMPRODUCT route which is an array type formula and will be slower depending on the number of rows. – Scott Craner May 04 '17 at 15:47
  • Yes, they will always be the same. For all filtered data, all values in column AU should be returned if the value in Column Q matches cell AJ3. I need the formula to be dynamic though, so that any data later added will be included. – LoriLee May 04 '17 at 16:49
  • That is not what I stated. You are going to filter the data on a certain criteria, say column A = "Foo" or something like that. do you always filter the data on one thing or will it be multiple. Say next time it filters on Column B >= 200. if that is the case the only way is SUMPRODUCT and OFFSET. Which is slow. The only other way around it is to use vba. But that has its own drawbacks. – Scott Craner May 04 '17 at 16:59
  • AJ3 ("Closed") is my criteria. It will never change. The formula will always look for my criteria in Column Q. If found, it will always add the numbers in Column AU. Criteria and lookup locations (columns) are all static. – LoriLee May 04 '17 at 17:20
  • I was able to get what I wanted with this formula: =(SUMIFS(AU:AU,Q:Q,AJ3)). EXCEPT that I also need it to only provide me the total of the filtered items. I've tried: =SUBTOTAL(9,SUMIF(Q:Q,"=AJ3",AU:AU)) and a few variances and still no luck. :-( This is frustrating. – LoriLee May 04 '17 at 21:13
  • Last time, the only way you are going to get that is to use the SUMPRODUCT/OFFSET with your variable range. There is no other way to sum filtered data with criteria. Or you can write your own vba UDF that loops through the range and checks if row is hidden or not. You are not going to be able to do it with formulas any other way. – Scott Craner May 05 '17 at 01:13
  • Okay, apparently that point was lost in translation. Well, VBA, here I come! Thanks for the education. – LoriLee May 05 '17 at 01:22

0 Answers0