Adding up cells which fulfill certain criteria

[xl_psychic]

Hi,

I am facing a problem while trying to get an average of numbers.

I have Batch Numbers in Cell Range D16:D16 (1,2,3,4,etc.....) and I have Scores in Cell Range G16:J186 [Call 1 (Column G), Call 2 (Column H), Call 3 (Column I)and Call 4 (Column J)].

I am trying to find out the Average Scores for the entire month for a particular batch in question. I have used the following formula but it doesn't gimme the sum:

=SUMPRODUCT(--('Monthly Snapshot'!$D$16:$D$186=$C$6),--('Monthly Snapshot'!$G$16:$J$186=">=70"),'Monthly Snapshot'!$G$16:$J$186)

Where Monthly Snapshot is the name of the sheet from which the scores are being taken and batch number is being matched.
C6 is the Drop Down list for Batch Number.

The scores in the cell range are between 1 to 20 or either 70, 80, 90 and 100.

How do I go about doing the same.

Please help

Thank you

Rohan Fernandes

 

[Andrew Poulsom]

Maybe:

=SUMPRODUCT(--('Monthly Snapshot'!$D$16:$D$186=$C$6),--('Monthly Snapshot'!$G$16:$J$186>=70),'Monthly Snapshot'!$G$16:$J$186)

Putting quotes around the test results in a search for that string.

 

[Joe4]

I kind of got lost in your details, but if you are trying to do a conditional average, would somehting like this work?

=SUMIF(...)/COUNTIF(...)

 

[xl_psychic]

Thanks Andrew..

It solved the problem.

 

[PaddyD]

hi cesloan - welcome to the board!

you should start a new thread for new topics, even if they're related to previous ones (in which case, you're better off posting a link in your new thread).