ForcedInduction
New Member
- Joined
- Apr 5, 2019
- Messages
- 1
I am trying to determine if there is a way to add multiple “IF”like functions to the troublesome issue of countif/averageif during a subtotal.
For Instance, I retrieve my data from an internal server atwork that gives me a spreadsheet with times listed in column B, the type of theoccurrence/number of that type of occurrence in column F (eg. ABC 1234567 whereABC is the type and 1234567 are stand-ins for the that number), the totalresult of the occurrence in Column G, and a variety of data for the occurrencein Columns H:DL.
Currently I have to filter by date first and then filter byeach individual type of occurrence and then also by the total result of the occurrencewhen determining my averages if I just use a =Subtotal(1,) function. It wouldbe a lot easier to do a subtotal(averageif like function, but those don’t exist and a SUMPRODUCT(SUBTOTALfunction needs to be used instead.
I’ve always seen those kinds of functions set up to haveessentially only 1 if statement contained within them. Such as below where I’mcounting the number of occurrences of my current selection that resulted in a value of “H.”
=SUMPRODUCT(SUBTOTAL(3,OFFSET($G:$G,ROW($G$11:$G$300000)-ROW($G$11),,1)),ISNUMBER(SEARCH("H",$G$11:$G$300000))+0)
If there a way to expand this so that I can do somethinglike Average the cells in J10:J300000, ifthe corresponding value in column G is “A” or “P” AND the prefix of Column F issomething like “DWF”?
For Instance, I retrieve my data from an internal server atwork that gives me a spreadsheet with times listed in column B, the type of theoccurrence/number of that type of occurrence in column F (eg. ABC 1234567 whereABC is the type and 1234567 are stand-ins for the that number), the totalresult of the occurrence in Column G, and a variety of data for the occurrencein Columns H:DL.
Currently I have to filter by date first and then filter byeach individual type of occurrence and then also by the total result of the occurrencewhen determining my averages if I just use a =Subtotal(1,) function. It wouldbe a lot easier to do a subtotal(averageif like function, but those don’t exist and a SUMPRODUCT(SUBTOTALfunction needs to be used instead.
I’ve always seen those kinds of functions set up to haveessentially only 1 if statement contained within them. Such as below where I’mcounting the number of occurrences of my current selection that resulted in a value of “H.”
=SUMPRODUCT(SUBTOTAL(3,OFFSET($G:$G,ROW($G$11:$G$300000)-ROW($G$11),,1)),ISNUMBER(SEARCH("H",$G$11:$G$300000))+0)
If there a way to expand this so that I can do somethinglike Average the cells in J10:J300000, ifthe corresponding value in column G is “A” or “P” AND the prefix of Column F issomething like “DWF”?
