Help with Sumif and Averageif

[bkurway]

I am working on updating a Excel sheet that i inherited from my predecessor, and trying to remember all the formulas i learned from high school and i believe i need to use the Sumif and Average if formula.

The first sheet is where all the data gets dumped into.

On the second sheet I want to have a Sum and Average of Column D in the red boxes, based on the text in Column C.


Any text you can offer is greatly appreciated.

Thanks,
Bobby Kenny

[Paul]

For the sum, using SUMIF:

=SUMIF('Sheet A'!C:C,"A101",'Sheet A'!D:D)
=SUMIF('Sheet A'!C:C,"A202",'Sheet A'!D:D)

For the averages, using the array formula AVERAGE(IF:

=AVERAGE(IF('Sheet A'!C2:C300="A101",'Sheet A'!D2:D300))
=AVERAGE(IF('Sheet A'!C2:C300="A202",'Sheet A'!D2:D300))

After entering or modifying array formulas you must press CTRL+SHIFT+ENTER. You could also use a non-array version, e.g. SUMPRODUCT/COUNT or other, but this should work just fine.