Filtering a Matrix w/ a Vector and Summing/Counting Values in a Column

[u8366]

Seeking your help to determine an array formula that will filter the values in the first column of a table (multiple rows and columns) with the values in an array and summing or counting specific values in a column of the remaining unfiltered rows.

The data table consists of the following on one tab in the workbook and is a named range, Person_Lookup

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Weight[/TD]
[TD]Certification[/TD]
[/TR]
[TR]
[TD]Ann[/TD]
[TD]120[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]180[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD]160[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Jose[/TD]
[TD]140[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]140[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Nancy[/TD]
[TD]135[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Rick[/TD]
[TD]175[/TD]
[TD]No[/TD]
[/TR]
</tbody>[/TABLE]














The array value is on another tab, it's a named range and will typically include blank values, Current_Friends.

Ann
Carl
Jose
Rick
blank
blank
blank
...
blank

I'd like an array formula that can sum the weight of the friends listed in the array and another that can count the number of friends with certification.

Is this possible? Thanx a bunch for your insight and experience.

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Maybe...

1. Sum of the weights
=SUMPRODUCT(SUMIF(INDEX(Person_Lookup,0,1),Current_Friends,INDEX(Person_Lookup,0,2)))

2. Count with certification (Excel 2007 or higher)
=SUMPRODUCT(COUNTIFS(INDEX(Person_Lookup,0,1),Current_Friends,INDEX(Person_Lookup,0,3),"Yes"))

Hope this helps

M.

 

[u8366]

Thanx for the prompt response Marcelo. I get a #REF! error when translating this to my need and entering it as an array formula. I don't understand the 0 as the row reference in the INDEX functions.

..... Rick

 

[Marcelo Branco]

Thanx for the prompt response Marcelo. I get a #REF! error when translating this to my need and entering it as an array formula. I don't understand the 0 as the row reference in the INDEX functions.

..... Rick

You don't need to confirm the formulas with Ctrl+Shift+Enter

A 0 (zero) as the row reference means all rows - see the help file.

So
INDEX(Person_Lookup,0,1)
returns an array of values corresponding to all rows of the first column (3rd argument = 1) of the named range Person_Lookup.

I tested both formulas and they worked perfectly for me.

M.

 

[Marcelo Branco]

Rick,

Please, check the scope of the named ranges.

Both must have Scope = Workbook

M.

 

[u8366]

Thanx Marcelo for your continued support. The scope was the issue!

 

[Marcelo Branco]

Thanx Marcelo for your continued support. The scope was the issue!

Rick,

You are welcome. Glad for helping.

M.