M
Minnesotaxls
Hello,
I stumbled upon the SUBTOTAL function a while ago and found it quite
useful for summing filtered lists. Now I would like to take it a step
further.
Is it possible to use the IF function within an array formula to match
a certain value within a list, and then use the SUBTOTAL function to
sum only the values that the filters specify?
I have a table of data in which category column headers appear from
left to right. These category headers are rows within a filterable list
on another tab within the worksheet.
Currently, I have a SUMIF function that looks like this:
=SUMIF('EXPENSE LIST'!$E$3:$F$65536, GRAPHS!A4, 'EXPENSE
LIST'!$C$3:$C$65536)
This formula says, go find the category header (GRAPHS!A4) within the
'EXPENSE LIST'!$E$3:$F$65536 range; if you find it, then SUM the values
within the 'EXPENSE LIST'!$C$3:$C$65536 range.
This is nice, but it doesn't allow me to filter the EXPENSE LIST to
obtain different totals for the category header; it simply sums all
values that match the category header.
I almost want the same result that a SUMIF function would give, except
I want the data summed to be only the rows not filtered out. The
result I want would be a table that could change based on the values
filtered out in the EXPENSE LIST. In other words, I want Microsoft to
write a SUBTOTALIF function. But for now, I don't know what I'll do!
Can ARRAY formulas be used with a SUBTOTAL function?
-Drew
I stumbled upon the SUBTOTAL function a while ago and found it quite
useful for summing filtered lists. Now I would like to take it a step
further.
Is it possible to use the IF function within an array formula to match
a certain value within a list, and then use the SUBTOTAL function to
sum only the values that the filters specify?
I have a table of data in which category column headers appear from
left to right. These category headers are rows within a filterable list
on another tab within the worksheet.
Currently, I have a SUMIF function that looks like this:
=SUMIF('EXPENSE LIST'!$E$3:$F$65536, GRAPHS!A4, 'EXPENSE
LIST'!$C$3:$C$65536)
This formula says, go find the category header (GRAPHS!A4) within the
'EXPENSE LIST'!$E$3:$F$65536 range; if you find it, then SUM the values
within the 'EXPENSE LIST'!$C$3:$C$65536 range.
This is nice, but it doesn't allow me to filter the EXPENSE LIST to
obtain different totals for the category header; it simply sums all
values that match the category header.
I almost want the same result that a SUMIF function would give, except
I want the data summed to be only the rows not filtered out. The
result I want would be a table that could change based on the values
filtered out in the EXPENSE LIST. In other words, I want Microsoft to
write a SUBTOTALIF function. But for now, I don't know what I'll do!
Can ARRAY formulas be used with a SUBTOTAL function?
-Drew