Substitute SUMIF with basic functions

[xvanic04]

Hello,
is it possible to replace SUMIF with basic functions like SUM, IF... ?
To go through a named range and sum all records fulfilling some condition.
I want to know the principle, because I have to use a different action than SUM in my task.

Thanks for your help.

Best Regards.

 

[GlennUK]

What action do you have to use, instead of SUM? Explain what you are trying to do.

 

[xvanic04]

There are more actions and I am still considering them. Is it possible to replace SUMIF easilly? Otherwise I will solve the whole problem in VBA. Thanks.

 

[GlennUK]

I can just show you the SUMIF alternative then, as you can't say what you are aiming for ... an array formula ( entered using Ctrl-Shift-Enter instead of Enter ):

=SUM(IF(myrange=testvalue,sumrange))

 

[Jon von der Heyden]

By alternatives do you mean different functions?

Excel Workbook
	A	B	C	D	E	F
1					Criteria:	c
2	Category	Value			CSE	NON CSE
3	a	60		SUM	96	96
4	b	35		COUNT	3	3
5	c	33		AVERAGE	32	32
6	d	49		MIN	10
7	a	43		MAX	53
8	b	82
9	c	10
10	d	43
11	a	19
12	b	18
13	c	53
14	d	76
Sheet1

Excel 2010

Cell Formulas
Range	Formula
F3	=SUMIF($A$3:$A$14,$F$1,$B$3:$B$14)
F4	=COUNTIF($A$3:$A$14,$F$1)
F5	=SUMIF($A$3:$A$14,$F$1,$B$3:$B$14)/COUNTIF($A$3:$A$14,$F$1)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[xvanic04]

Thanks very much for help.
That is exactly what I need.