Sum variable number of rows with formula

[malisesaucier]

I would like to be able to copy formula down to sum amounts in COL B based on how many text values in COL A. I have searched to no avail. Is this possible without VBA and without array (would prefer to avoid volatile functions if at all possible)? (Can't use these when uploaded to secondary program) This is simple example of problem.

TIA,
malise

abc	1			10
def	2
ghi	3
jkl	4
abc	1			6
def	2
ghi	3
abc	1			3
def	2

<tbody>
</tbody>

 

[Marcelo Branco]

Can your formula be modified to only sum the text, without an array formual?

Maybe this...

	A​	B​	C​
1​	Text/Num​	Value​	Sum​
2​	abc​	1​	10​
3​	def​	2​
4​	ghi​	3​
5​	jkl​	4​
6​	44​	5​
7​
8​	abc​	1​	8​
9​	12​	2​
10​	def​	3​
11​	ghi​	4​
12​
13​
14​	abc​	1​	3​
15​	def​	2​
16​	4​	3​
17​
18​
19​	10​	1​	27​
20​	abc​	2​
21​	def​	3​
22​	ghi​	4​
23​	jkl​	5​
24​	mno​	6​
25​	pqr​	7​

Formula in C2 copied down
=IF(AND(A2<>"",OR(A1="Text/Num",A1="")),SUMPRODUCT(--ISTEXT(A2:$A$1000),B2:$B$1000)-SUM(C3:$C$1000),"")

M.

 

[Marcelo Branco]

I think this may do it, but I need to test further:

=IF(AND(A2<>"",OR(A1="Text",A1="")),SUMPRODUCT(--ISTEXT(A2:$A$1001),B2:$B$1001)-SUM(C3:$C$1000),"")

Yes, it seems ok

M.