Subtotal Function and Countif/Sumif

[SpikeGiles]

The subtotal function is great when working with data you want to filter several ways, but it lacks the functionality that would exist if it had a countif and a sumif code, unless I am missing something. Does this functionality exist within Excel? If not, is there a work-around?

 

[yanivp1]

I want to combine to formulas:


The first:
=COUNTIF(G8:G110,"2")+COUNTIF(G8:G110,"1")


The second:
=SUBTOTAL(9,G8:G110)


Is it possible?


Thanks for any help!

 

[Aladin Akyurek]

I want to combine to formulas:


The first:
=COUNTIF(G8:G110,"2")+COUNTIF(G8:G110,"1")


The second:
=SUBTOTAL(9,G8:G110)


Is it possible?


Thanks for any help!

See the reply to your other post: https://www.mrexcel.com/forum/excel-questions/900611-using-subtotal-countifs.html

 

[yanivp1]

I'm not an excel proffesional and don't know how to use to information at the link for my needed formula.

 

[Wad Mabbit]

Hi,
I'm trying to figure a way to count where a single criteria appears in two columns. I have got this, but it's double the number. I could just halve the result, but is there a more elegant way to do this?

=SUMPRODUCT(
SUBTOTAL(3,OFFSET(
Column 4:Column 5,
ROW(Column 4:Column 5)-MIN(ROW(Column 4:Column 5)), , 1)),
--(Column 4="S"),--(Column 5="S"))

 

[Aladin Akyurek]

Hi,
I'm trying to figure a way to count where a single criteria appears in two columns. I have got this, but it's double the number. I could just halve the result, but is there a more elegant way to do this?

=SUMPRODUCT(
SUBTOTAL(3,OFFSET(
Column 4:Column 5,
ROW(Column 4:Column 5)-MIN(ROW(Column 4:Column 5)), , 1)),
--(Column 4="S"),--(Column 5="S"))

Would you post a scaled-down sample using XL2BB?

 

[Wad Mabbit]

Hi,
I got it to work, but it spawns arrays.

I've moved away from that combo as it's volatile and used helper columns instead. Especially for non-contiguous ranges.

It would be nice to collate hits but limitations of tables, filters limit choices.
Such is life..

 

[Rahul1987]

The subtotal function is great when working with data you want to filter several ways, but it lacks the functionality that would exist if it had a countif and a sumif code, unless I am missing something. Does this functionality exist within Excel? If not, is there a work-around?

There are two ways to it

1. =SUM((C2:C7=C12)*(D2:D7))
2. =SUMPRODUCT(--(C2:C7=C12),--(D2:D7))

1​	Record	Type	Amt	Filter
2​	1​	Tax	1200​	A
3​	1​	Fin	5200​	B
4​	2​	Tax	4500​	C
5​	2​	Fin	3500​	B
6​	3​	Tax	4600​	B
7​	3​	Fin	5700​	B
8​
9​		TAX	4600​
10​		FIN	14400​
11​		TAX
12​		TAX	10300		10300​
		1​	10300​	2​	10300​