Subtotal and Sumif combination help

S

Sourkraut

New Member
Joined
Jun 19, 2009
Messages
35
Alright, I need help combining two formulas.

What we need is to subtotal a range based on a criteria and we need that subtotal to change when the range is filtered.

We have two formulas that work seperately but we need to combine them into one.

=SUBTOTAL(9,Summary!H3:H39)

=SUMIF(Summary!F3:F39,B35,Summary!H3:H39)

B35 = The name we're trying reference
Column F is the column that would hold that name
Column H has the values we want to sum
 
Sort by date Sort by votes
ianlnai said:
hi..i need help with this formula :

Capture.jpg
[/URL][/IMG]

i need to combine these two formula :
- SUMIF($B$2:$B$17,$B21,$C$2:$C$17)
- SUBTOTAL(9,$C$2:$C$17)

thx before
Click to expand...

C21, copied down:
Rich (BB code): 

=SUMPRODUCT(
    SUBTOTAL(9,OFFSET($C$2,ROW($C$2:$C$17)-ROW($C$2),0,1)),
    --($B$2:$B$17=$B21))

By the way, you can use better methods for posting useable samples instead images, like http://www.mrexcel.com/forum/about-board/508133-attachments.html or https://app.box.com/s/soezox25h3w0q5s4rcyl.
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Aladin Akyurek said:
C21, copied down:
Rich (BB code): 

=SUMPRODUCT(
    SUBTOTAL(9,OFFSET($C$2,ROW($C$2:$C$17)-ROW($C$2),0,1)),
    --($B$2:$B$17=$B21))

By the way, you can use better methods for posting useable samples instead images, like http://www.mrexcel.com/forum/about-board/508133-attachments.html or https://app.box.com/s/soezox25h3w0q5s4rcyl.
Click to expand...


THX before...sorry just now to feedback...noted for the posting method

i like ask new formula for this situation :


ABCDEFGH
TOTAL
GRAND TOTAL

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: center"]SORT[/TD]
[TD="align: center"]ITEM[/TD]
[TD="align: center"]help1[/TD]
[TD="align: center"]Value[/TD]
[TD="align: center"]HELP2[/TD]

[TD="align: center"]A1[/TD]
[TD="align: center"]15[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]B1[/TD]
[TD="align: center"]12[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A2[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]C1[/TD]
[TD="align: center"]13[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A1[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]A2[/TD]
[TD="align: center"]14[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]A3[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]B2[/TD]
[TD="align: center"]17[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD="align: center"]TOTAL[/TD]

[TD="bgcolor: #FFFF00, align: center"]0[/TD]
[TD="bgcolor: #FFFF00, align: center"]0[/TD]

[TD="align: center"]C2[/TD]
[TD="align: center"]18[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B2[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]A3[/TD]
[TD="align: center"]19[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]B3[/TD]
[TD="align: center"]11[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B3[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]0[/TD]

[TD="align: center"]C3[/TD]
[TD="align: center"]20[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B2[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]
[TD="align: center"]TOTAL[/TD]

[TD="bgcolor: #FFFF00, align: center"]0[/TD]
[TD="bgcolor: #FFFF00, align: center"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C2[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C3[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]15[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C2[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]16[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]C1[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]17[/TD]

[TD="bgcolor: #FFFF00, align: center"]0[/TD]
[TD="bgcolor: #FFFF00, align: center"]0[/TD]

[TD="bgcolor: #CACACA, align: center"]18[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
C2=B2&A2
D2=IF(A2="TOTAL",SUBTOTAL(9,#REF!),VLOOKUP(C2,$G$1:$H$9,2,FALSE))
E2=IF(A2="TOTAL",SUBTOTAL(9,#REF!),0)
C3=B3&A3
D3=IF(A3="GRAND TOTAL",SUBTOTAL(9,E$2:E2),IF(A3="TOTAL",SUBTOTAL(9,#REF!)-(SUBTOTAL(9,E$2:E2)*2),VLOOKUP(C3,$G$1:$H$9,2,FALSE)))
E3=IF(A3="TOTAL",SUBTOTAL(9,D$2:D2),0)
C4=B4&A4
D4=IF(A4="GRAND TOTAL",SUBTOTAL(9,E$2:E3),IF(A4="TOTAL",SUBTOTAL(9,#REF!)-(SUBTOTAL(9,E$2:E3)*2),VLOOKUP(C4,$G$1:$H$9,2,FALSE)))
E4=IF(A4="TOTAL",SUBTOTAL(9,D$2:D3),0)
C5=B5&A5
D5=IF(A5="GRAND TOTAL",SUBTOTAL(9,E$2:E4),IF(A5="TOTAL",SUBTOTAL(9,$D1:D4)-(SUBTOTAL(9,E$2:E4)*2),VLOOKUP(C5,$G$1:$H$9,2,FALSE)))
E5=IF(A5="TOTAL",SUBTOTAL(9,D$2:D4),0)
D6=IF(A6="GRAND TOTAL",SUBTOTAL(9,E$2:E5),IF(A6="TOTAL",SUBTOTAL(9,$D2:D5)-(SUBTOTAL(9,E$2:E5)*2),VLOOKUP(C6,$G$1:$H$9,2,FALSE)))
E6=IF(A6="TOTAL",SUBTOTAL(9,D$2:D5),0)
C7=B7&A7
D7=IF(A7="GRAND TOTAL",SUBTOTAL(9,E$2:E6),IF(A7="TOTAL",SUBTOTAL(9,$D3:D6)-(SUBTOTAL(9,E$2:E6)*2),VLOOKUP(C7,$G$1:$H$9,2,FALSE)))
E7=IF(A7="TOTAL",SUBTOTAL(9,D$2:D6),0)
C8=B8&A8
D8=IF(A8="GRAND TOTAL",SUBTOTAL(9,E$2:E7),IF(A8="TOTAL",SUBTOTAL(9,$D4:D7)-(SUBTOTAL(9,E$2:E7)*2),VLOOKUP(C8,$G$1:$H$9,2,FALSE)))
E8=IF(A8="TOTAL",SUBTOTAL(9,D$2:D7),0)
C9=B9&A9
D9=IF(A9="GRAND TOTAL",SUBTOTAL(9,E$2:E8),IF(A9="TOTAL",SUBTOTAL(9,$D5:D8)-(SUBTOTAL(9,E$2:E8)*2),VLOOKUP(C9,$G$1:$H$9,2,FALSE)))
E9=IF(A9="TOTAL",SUBTOTAL(9,D$2:D8),0)
C10=B10&A10
D10=IF(A10="GRAND TOTAL",SUBTOTAL(9,E$2:E9),IF(A10="TOTAL",SUBTOTAL(9,$D6:D9)-(SUBTOTAL(9,E$2:E9)*2),VLOOKUP(C10,$G$1:$H$9,2,FALSE)))
E10=IF(A10="TOTAL",SUBTOTAL(9,D$2:D9),0)
C11=B11&A11
D11=IF(A11="GRAND TOTAL",SUBTOTAL(9,E$2:E10),IF(A11="TOTAL",SUBTOTAL(9,$D7:D10)-(SUBTOTAL(9,E$2:E10)*2),VLOOKUP(C11,$G$1:$H$9,2,FALSE)))
E11=IF(A11="TOTAL",SUBTOTAL(9,D$2:D10),0)
D12=IF(A12="GRAND TOTAL",SUBTOTAL(9,E$2:E11),IF(A12="TOTAL",SUBTOTAL(9,$D8:D11)-(SUBTOTAL(9,E$2:E11)*2),VLOOKUP(C12,$G$1:$H$9,2,FALSE)))
E12=IF(A12="TOTAL",SUBTOTAL(9,D$2:D11),0)
C13=B13&A13
D13=IF(A13="GRAND TOTAL",SUBTOTAL(9,E$2:E12),IF(A13="TOTAL",SUBTOTAL(9,$D9:D12)-(SUBTOTAL(9,E$2:E12)*2),VLOOKUP(C13,$G$1:$H$9,2,FALSE)))
E13=IF(A13="TOTAL",SUBTOTAL(9,D$2:D12),0)
C14=B14&A14
D14=IF(A14="GRAND TOTAL",SUBTOTAL(9,E$2:E13),IF(A14="TOTAL",SUBTOTAL(9,$D10:D13)-(SUBTOTAL(9,E$2:E13)*2),VLOOKUP(C14,$G$1:$H$9,2,FALSE)))
E14=IF(A14="TOTAL",SUBTOTAL(9,D$2:D13),0)
C15=B15&A15
D15=IF(A15="GRAND TOTAL",SUBTOTAL(9,E$2:E14),IF(A15="TOTAL",SUBTOTAL(9,$D11:D14)-(SUBTOTAL(9,E$2:E14)*2),VLOOKUP(C15,$G$1:$H$9,2,FALSE)))
E15=IF(A15="TOTAL",SUBTOTAL(9,D$2:D14),0)
C16=B16&A16
D16=IF(A16="GRAND TOTAL",SUBTOTAL(9,E$2:E15),IF(A16="TOTAL",SUBTOTAL(9,$D12:D15)-(SUBTOTAL(9,E$2:E15)*2),VLOOKUP(C16,$G$1:$H$9,2,FALSE)))
E16=IF(A16="TOTAL",SUBTOTAL(9,D$2:D15),0)
D17=IF(A17="GRAND TOTAL",SUBTOTAL(9,E$2:E16),IF(A17="TOTAL",SUBTOTAL(9,$D13:D16)-(SUBTOTAL(9,E$2:E16)*2),VLOOKUP(C17,$G$1:$H$9,2,FALSE)))
E17=IF(A17="TOTAL",SUBTOTAL(9,D$2:D16),0)
D18=IF(A18="GRAND TOTAL",SUBTOTAL(9,E$2:E17),IF(A18="TOTAL",SUBTOTAL(9,$D14:D17)-(SUBTOTAL(9,E$2:E17)*2),VLOOKUP(C18,$G$1:$H$9,2,FALSE)))
E18=IF(A18="TOTAL",SUBTOTAL(9,D$2:D17),0)

<tbody>
</tbody>

<tbody>
</tbody>

i want every total will sum automatically, for the logic i add column E to help the calculation. so each total will ( subtotal at column D ) and ( minus from subtotal at column E * 2 ).

but the result is not showing what i want...

can u show me the better formula for this kind situation ?

thx for the help

waiting for the formula :)
 
Upvote 0
ianlnai said:
THX before...sorry just now to feedback...
Click to expand...

Does that mean the formula you are given is ok?

noted for the posting method

i like ask new formula for this situation :
[...]
Click to expand...

Instead of showing tons of non-working formulas and their results, just show the desired results appropriate to the sample along a concise explanation in words.
 
Upvote 0
barry houdini said:
Sorry I got the ranges the wrong way round, this should fix it.....

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Summary!H3,ROW(Summary!H3:H39)-ROW(Summary!H3),0)),(Summary!F3:F39=B35)+0)
Click to expand...

This is great. Can you tell me the purpose of using offset in this formula? Why won't SUBTOTAL(9,Summary!H3:H39) be sufficient?
 
Upvote 0
Hi there,

I have something similar to this. In Laymans terms: I'm looking at a column of invoice totals, and want to sum them if the criteria in one column is 'Closed' and the criteria in another column is '0-30' OR '31-45'. I need to be able to filter the column, hence the need for subtotal too.

My column to sum is AE:AE (more specifically AE17:AE3021), then i am looking in AC:AC for "Closed" and AH:AH for "0-30" and "31-45".

=SUMPRODUCT(--(AC:AC="Closed"),--(AH:AH="0-30"),--(AH:AH="31-45"),SUBTOTAL(9,OFFSET($AE$17,ROW($AE:$AE)-ROW(AE$17),,1)))

Current result is 0 (zero). Assistance greatly appreciated!
 
Upvote 0
mg259usa said:
Hi there,

I have something similar to this. In Laymans terms: I'm looking at a column of invoice totals, and want to sum them if the criteria in one column is 'Closed' and the criteria in another column is '0-30' OR '31-45'. I need to be able to filter the column, hence the need for subtotal too.

My column to sum is AE:AE (more specifically AE17:AE3021), then i am looking in AC:AC for "Closed" and AH:AH for "0-30" and "31-45".

=SUMPRODUCT(--(AC:AC="Closed"),--(AH:AH="0-30"),--(AH:AH="31-45"),SUBTOTAL(9,OFFSET($AE$17,ROW($AE:$AE)-ROW(AE$17),,1)))

Current result is 0 (zero). Assistance greatly appreciated!
Click to expand...

Like this:

=SUMPRODUCT(--(AC17:AC3021="Closed"),--ISNUMBER(MATCH(AH17:AH3021,{"0-30","31-45"},0)),SUBTOTAL(9,OFFSET($AE$17,ROW(AE17:AE3021)-ROW(AE$17),,1)))

It's better to avoid referencing whole columns in this type of formulas.
 
Upvote 0
Can you help me? I need to combine a sumif and a subtotal in Excel.
like =SUBTOTAL(109;D4:D17) and =Sumif($K$4:$K17;"OK";D4:D17).
Thanks to all!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Simply current workload. Pivot table?
Replies
0
Views
159
ShadowRider
S
M
  • Solved
Sum the values between two specified column headings - number of columns between will change
2
Replies
14
Views
247
Moley84
M
N
  • Solved
Sumproduct with Table Rows doesn't work
Replies
8
Views
249
Nicha
N
K
  • Question Question
help with formula
Replies
3
Views
65
Peter_SSs
Peter_SSs
LearningByDoing
  • Solved
GROUPBY + Slicer
Replies
5
Views
56
Fluff
Fluff

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

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