How to use sumifs that have same SUM result with subtotal 9

eddorena

New Member
Joined
Dec 3, 2021
Messages
47
Office Version
  1. 365
Platform
  1. Windows
Hi Guys, this is my first post after 2 yrs as silent student ?

I have some question how to get the same result by using SUMIFS vs SUBTOTAL 9

The case is: I am set filter from 3 different columns and the subtotal 9 sum the rest of the number is 10,000,000. However, if I use formula SUMIFS with the same filter, the result not same

Formula

=SUMIFS(AB:AB,E:E,"OK",F:F,"ONSITE",H:H,"HRS") result formula 9,200,000

by using filters with same key in columns E, F & H the SUBTOTAL result above top line of cell is 10,000,000 there GAP around 800,000

may be the right Question is : How to use Subtotal 9 with condition :(
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Yeah.... u right, it's work 100% and still can modified for some additional purpose, thanks mate I appreciate
This is the Mini Sheet

Excel-SUBTOTAL with condition.xlsx
ABCDEF
1480=SUMPRODUCT(SUBTOTAL(109,OFFSET(D4,ROW(D:D)-ROW(D4),)),--(A:A="Yes"),--(B:B="B"),--(C:C="BL"))
25070=SUBTOTAL(109,D:D)
3CatCat2Cat3Sum
4YesABL20
5NoABL30
6NoABL40
7YesBPL10
8yesBBL10
9YesBBL20
10NoABL20
11NoABL30
12YesBPL60
13YesDPL90
14NoABL30
15NoABL40
16YesBPL10
17yesBBL10
18YesBBL20
19NoABL20
20NoABL30
21YesBPL60
22YesDPL90
23NoABL30
24NoABL40
25YesBPL10
26yesBBL10
27YesBBL20
28NoABL20
29NoABL30
30YesBPL60
31YesDPL90
32NoABL30
33NoABL40
34YesBPL10
35yesBBL10
36YesBBL20
37NoABL20
38NoABL30
39YesBPL60
40YesDPL90
41NoABL30
42NoABL40
43YesBPL10
44yesBBL10
45YesBBL20
46NoABL20
47NoABL30
48YesBPL60
49YesDPL90
50NoABL30
51NoABL40
52YesBPL10
53yesBBL10
54YesBBL20
55NoABL20
56NoABL30
57YesBPL60
58YesDPL90
59NoABL30
60NoABL40
61YesBPL10
62yesBBL10
63YesBBL20
64NoABL20
65NoABL30
66YesBPL60
67YesDPL90
68NoABL30
69NoABL40
70YesBPL10
71yesBBL10
72YesBBL20
73NoABL20
74NoABL30
75YesBPL60
76YesDPL90
77YesABL20
78NoABL30
79NoABL40
80YesBPL10
81yesBBL10
82YesBBL20
83NoABL20
84NoABL30
85YesBPL60
86YesDPL90
87NoABL30
88NoABL40
89YesBPL10
90yesBBL10
91YesBBL20
92NoABL20
93NoABL30
94YesBPL60
95YesDPL90
96NoABL30
97NoABL40
98YesBPL10
99yesBBL10
100YesBBL20
101NoABL20
102NoABL30
103YesBPL60
104YesDPL90
105NoABL30
106NoABL40
107YesBPL10
108yesBBL10
109YesBBL20
110NoABL20
111NoABL30
112YesBPL60
113YesDPL90
114NoABL30
115NoABL40
116YesBPL10
117yesBBL10
118YesBBL20
119NoABL20
120NoABL30
121YesBPL60
122YesDPL90
123NoABL30
124NoABL40
125YesBPL10
126yesBBL10
127YesBBL20
128NoABL20
129NoABL30
130YesBPL60
131YesDPL90
132NoABL30
133NoABL40
134YesBPL10
135yesBBL10
136YesBBL20
137NoABL20
138NoABL30
139YesBPL60
140YesDPL90
141NoABL30
142NoABL40
143YesBPL10
144yesBBL10
145YesBBL20
146NoABL20
147NoABL30
148YesBPL60
149YesDPL90
150NoDNL70
Sample
Cell Formulas
RangeFormula
E1E1=SUMPRODUCT(SUBTOTAL(109,OFFSET(D4,ROW(D:D)-ROW(D4),)),--(A:A="Yes"),--(B:B="B"),--(C:C="BL"))
E2E2=SUBTOTAL(109,D:D)
Named Ranges
NameRefers ToCells
_FilterDatabase=Sample!$A$3:$D$150E1
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top