Help With Calculating Test Results

[Jaevwyn]

Hi,

I am trying to find a better way of calculating total metrics of our test results from various sets of tests.
The cut down table and the text below shows how we are currently doing it, but the formula is going to be too long to do it on the next sheet so I am looking for a more simple way of doing this. Cannot use VBA for this.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Total Pass Rate[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]Total Tests[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]Number of Tests[/TD]
[TD]Pass%[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]60%[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]25%[/TD]
[/TR]
</tbody>[/TABLE]
Our total results cell (B1) currently uses formula that Looks like this:
=(Line Pass Rate*(Line Number of Tests/Total Test Number)) one on these sections per line that I want in the totals.
In this case the formula would be =(B4*(A4/B2))+(B5*(A5/B2))+(B6*(A6/B2)).

I am trying to create a new table that contains many more test sets, possibly over a hundred lines and would like to avoid needing to doing the above formula section over a hundred times, one per line.
Currently the sheet does contain contents above and below the table so I don't think I can use any kind of formula that reference an entire column... I am a little stumped

Any and all help will be appreciated... fairly new here so apologies is something about the above post is wrong!

EDIT
I can't add extra columns or rows to this table and the totals area are not actually a part of the table, just added where they are on here for convenience

 

[Marcelo Branco]

Try

=SUMPRODUCT(A4:A6/B2,B4:B6)

M.

 

[Jaevwyn]

My Hero, looks like it works perfectly... never used this function before so I have something new to play with too!
Going to apply this to my larger table, thanks!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.