Average if and sumif

[jennamae]

Hi All,

I have two formulas I am trying to achieve and require some help

Attached if a sample spreadsheet

Please help

Also can do a vlookup from a pivot table?

[Pecoflyer]

The average is given by

=AVERAGEIF($A$1:$A$15,"=6",$B$1:$B$15)

(you can refer to a cell value instead of hard-coding the value)
Summing values in N

=SUMPRODUCT(($M$1:$M$10="gen")*($N$1:$N$10))

Although you asked for a count the result you gave is the sum of the values.

[Pecoflyer]

As for your last question you could either use the GETPIVOTDATA function ( not very flexible) or have a look at this article.
Using the free POWERPIVOT add-in also has it's advantages, but I can't help you much with that one.

[jennamae]

Thank you

Sum if worked perfectly

The average if, seems to work for only one argument, attached this is the actual workbook. Words for 6 but not 7?

[Pecoflyer]

This has to do with the way Excel treats numbers.
If you format your result with 10 or more decimals, it is not 7 but 6,99999...
As you formatted with 0 decimals what you see is 7, but XL sees 6,9999
The workaround is to wrap your formula with the ROUND function ( with 0 decimals). Then all should be OK.
Like

=ROUND((A4-FLOOR(A4,1))*24,0)

A little shorter is

=ROUND(MOD(A4,1)*24,0)