Multiple Ifs in Subtotal function

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”?

 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
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”?


Try this

<td >=SUMPRODUCT(SUBTOTAL(109,OFFSET(J5,ROW($J$5:$J$22)-ROW($J$5),,1))*($G$5:$G$22={"A","P"})*(ISNUMBER(SEARCH("DWF",$F$5:$F$22))+0))</td>
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
with prefix:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(J5,ROW($J$5:$J$22)-ROW($J$5),,1))*($G$5:$G$22={"A","P"})*(ISNUMBER(SEARCH("DWF",LEFT($F$5:$F$22,3)))+0))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,332
Messages
5,635,670
Members
416,871
Latest member
jbcpub

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top