0

I am making a interactive spreadsheet in which I dumped a bunch of data in one worksheet and then doing some quick analysis on another worksheet. From there I am making a Chart based on the analysis worksheet data. I was using this:

=COUNTIF('RFC Data'!G:G,"Work in Progress")

to pull data into the analysis worksheet and it worked great.

I had a tricky one where I used:

=COUNTIFS('RFC Data'!J:J,"4 - Low",'RFC Data'!T:T,"No",'RFC Data'!U:U,"No users affected")

where I had 3 criteria.

The issue is when I filter data worksheet it is not updating the analysis worksheet and in turn not the Chart.

So I updated the analysis worksheet to use:

=SUMPRODUCT(SUBTOTAL(3,OFFSET('RFC Data'!G:G,ROW('RFC Data'!G:G)-MIN(ROW('RFC Data'!G:G)),,1)),ISNUMBER(SEARCH("Work In Progress",'RFC Data'!G:G))+0)

which works great. The issue is I am having a bugger of a time to update the tricky one with =SUMPRODUCT.

I tried a few dozen things but no luck! Does anyone have suggestions?

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Why are you using ISNUMBER(SEARCH here? That would be equivalent to using a search criterion with wildcards (asterisks) within COUNTIF. Not that it's wrong, but you're asking Excel to do more work than is necessary. – XOR LX Sep 27 '16 at 21:35
  • I will give that a shot! Tyvm! –  Sep 27 '16 at 22:23
  • Updating first comment seeing I did not answer it... I have used that before and it seems to work... I will go back and "tune" it once it is completely working... –  Sep 27 '16 at 22:44
  • So have you tried simply adding further clauses similar to the one you have in the SUMPRODUCT equivalent to your additional COUNTIFS criteria? – XOR LX Sep 27 '16 at 23:07
  • Perhaps I am going about this wrong. The original issue is when I filter data worksheet it is not updating the analysis worksheet and in turn not the Chart. Is there a way to overcome this? In the past, I have had spreadsheets that did this, but for some reason it is not for this one... –  Sep 27 '16 at 23:27
  • I could try to use COUNTIFS but they do not appear to work with the filtering when I use it... –  Oct 04 '16 at 22:40
  • Sorry - I meant to say update your SUMPRODUCT/SUBTOTAL set-up. – XOR LX Oct 05 '16 at 05:50
  • I was able to make some headway. =SUMPRODUCT(SUBTOTAL(3,OFFSET(CalcRisk,ROW(CalcRisk)-MIN(ROW(CalcRisk)),,1))*((CalcRisk="4 - Low")*(ServiceDisruption="No")*(UsersAffected="No users affected")*(Type=”Minor”))) works but when I try to pull back an additional item, I am getting 0. =SUMPRODUCT(SUBTOTAL(3,OFFSET(CalcRisk,ROW(CalcRisk)-MIN(ROW(CalcRisk)),,1))*((CalcRisk="4 - Low")*(ServiceDisruption="No")*(UsersAffected="No users affected")*(Type=”Minor”)*(**"Type=”Significant”**))) See how I am trying to count the two items from Type. It is not working - I am getting 0 –  Oct 05 '16 at 08:49
  • You mean the entry in the Type column could be EITHER "Minor” OR ”Significant”? – XOR LX Oct 05 '16 at 08:57
  • I actually want the count of the total both. There are six different Types in total. Is that possible? I tried a bunch of different things but no luck. –  Oct 05 '16 at 13:27
  • Not sure what you mean. A given entry in that column can't simultaneously be "Significant" and "Minor". What was wrong with the logic in my previous statement that you didn't understood (or perhaps misunderstood), which I repeat: you want a count which for which the entry in the Type column could be EITHER "Minor” OR ”Significant”? – XOR LX Oct 05 '16 at 13:32
  • Either, yes... Sorry... –  Oct 05 '16 at 15:57
  • =SUMPRODUCT(SUBTOTAL(3,OFFSET(CalcRisk,ROW(CalcRisk)-MIN(ROW(CalcRisk)),,1)),0+(CalcRisk="4 - Low"),0+(ServiceDisruption="No"),0+(UsersAffected="No users affected"),0+ISNUMBER(MATCH(Type,{"Minor","Significant"},0))) – XOR LX Oct 05 '16 at 16:18
  • For some reason it does not like this portion of it - 0+ISNUMBER(MATCH(Types,{"Minor","Significant"},0))‌​ .... Note, I changed the name of one table from Type to Types for obvious reasons... I will keep playing with it... –  Oct 05 '16 at 18:39
  • I only copied the names from the formula you posted earlier! – XOR LX Oct 05 '16 at 20:01

0 Answers0