Formula to work out how many products among various products!

[MissConfussed]

Hi all,

Im new on this forum so I apologise if I dont explain my dilemma too well.

I have an assignment and one of the criteria is this..

Enter Formulae in cells H2:H5 that work out how many books, Dvd's, CD's and Computer games are in the spreadsheet.

Another criteria is

Repeat the above but work out the Total price of all the books, Dvd's, CD's and Computer games

I havent used spreadsheets for a while and im completely stuck.

Please help

If you can explain it as simply as possible I would be very grateful

Thanks

[AZ-XL]

h2 cell =COUNTIF(A2:$A$25,$G2)

i2 cell =SUMIF(A2:$A$25,$G2,C2:$C$25)

the drop down

[MissConfussed]

Hi, Thanks for the answer but this didn't work

[tigeravatar]

MissConfussed,

Welcome to the forum!
What you need are the ...if() function.
To count how many [item], use the Countif() function
To sum the price of [item], use the Sumif() function
To average the price of [item], you'll need to do Sumif()/Countif(), that is the Sumif() function divided by the Countif() function.

[AZ-XL]

=COUNTIF($A$2:$A$25,$G2)

=SUMIF($A$2:$A$25,$G2,C2:$C$25)

Itry this,
sorry for previous message, just now realized my error

[AZ-XL]

=SUMIF($A$2:$A$25,$G2,$C$2:$C$25)

I am too impatient and hasty

[MissConfussed]

I will try these now.. thanks for the help

[MissConfussed]

Ive managed to figure out the count criteria with the following formulae

=COUNTIF(B2:B25,"Book")

Is this right?

Im still stuck on the Total Formulae! Nothing seems to be working

[AZ-XL]

upload the will, like that will be easier for you

your formula above is correct, but instead of writing "Book" reference the cell that contains this word

[MissConfussed]

Hahahah.. Thanks! Wow, Its only taken me 2 hours of staring at my pc to try and figure this out!