# Multiple Ifs in Subtotal function

#### ForcedInduction

##### New Member
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
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
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))

Replies
3
Views
335
Replies
18
Views
5K
Replies
8
Views
415

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.

### Which adblocker are you using?

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

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