Sumif problem

[beroth]

I want to us the sumif as normal but I need the sum_range portion to have a formula incorporated with it.

Example: SUMIF(range,criteria,Average(sum_range)

or

SUMIF(range,criteria,SUM(C;C)*(F:F))


Is this possible in another function?

 

[bobhc]

Good day beroth


Can you not use AVERAGEIF or AVERAGEIFS they work with criteria without the need of the SUMIF

 

[beroth]

AVERAGEIF would no work as the quantities of the measurements I need averaged are listed in a separate column. Listing each measurement would add too much the the spreadsheet as well. What im looking for is a formula that will separate data in a particular range depending on the criteria, as with SUMIF, but instead of listing the sum range I want to be able to insert a formula.


Example: SUMIF(B:B,"Age 8",SUM((C:C)*(D:D))


Column C is the quantity of a particular age that are the same height

Column D is the Height of the Children Age 8 with various heights

Column B is the age of the children ranging in various ages


I need a formula that will; sum only a particular text in B:B with the formula C:C * D:D or what ever formula i need that would be associated with the text in B:B


Thank You

 

[Colin Legg]

The option I would choose:


In a spare column, eg cell E2, put the formula

=C2*D2

and then fill down the column. Then you can use

[code]=SUMIF(B:B,"Age 8",E:E)

Alternatively you can use SUMPRODUCT:

=SUMPRODUCT(--(B2:B100="Age 8&#34,C2:C100,D2:D100)[/code]


But the SUMPRODUCT formula is more complicated and expensive in terms of calculation time.

 

[Luke M]

Agree with what Colin said. Fastest/cheapest way is to use a helper column. SUMPRODUCT can do the job, but I wouldn't do a whole bunch especially if you need to reference an entire column.