INDEX, MATCH, VLOOKUP to Exclude

[MZING81]

Hi
I need to a formula to display any of the weeks total's that are greater than zero and excluding week 5. Is there a way to make a vlookup or an index/match to exclude criteria/data?

PERSON	WEEK	TOTAL
MRS.TWO	5	20
MR.ONE	5	40
MR.ONE	26	0
MR.THR	3	5
MR.THR	5	40
MR.R	5	10
MR.R	1	3
MRS.FOU	4	30
MRS.FOU	5	40
MR.SEVE	5	40
MRS.TEN	5	40
MRS.TEN	20	2
MR.ELEV	3	4
MR.ELEV	5	25

Thank You
MZing81

[MZING81]

Hi
I need to a formula to display any of the weeks total's that are greater than zero and excluding week 5. Is there a way to make a vlookup or an index/match to exclude criteria/data?

PERSON	WEEK	TOTAL
MRS.TWO	5	20
MR.ONE	5	40
MR.ONE	26	0
MR.THR	3	5
MR.THR	5	40
MR.R	5	10
MR.R	1	3
MRS.FOU	4	30
MRS.FOU	5	40
MR.SEVE	5	40
MRS.TEN	5	40
MRS.TEN	20	2
MR.ELEV	3	4
MR.ELEV	5	25

Thank You
MZing81

The idea is not to use a pivot table seeing that this already is one. I want a formula for it. It's going to be part of larger project with a generator. I have already put together an index and match formula with this table to pull data based on criteria from two columns. But the formula will me avoid using a autofilter in a macro which thru experience has been problematic for me.

[Ken Puls]

Assuming that your table starts in A1, and the name of your first subject is in G2:

=SUMPRODUCT(--($A$2:$A$15=G2),--($B$2:$B$15<>5),$C$2:$C$15)

[MZING81]

Assuming that your table starts in A1, and the name of your first subject is in G2:

=SUMPRODUCT(--($A$2:$A$15=G2),--($B$2:$B$15<>5),$C$2:$C$15)

Gave it a try and don't think that it worked was intended only got "0". Maybe I'm missing something. By subject in g2 do you mean the the week number being 5 in column G or the actual employee name? Gave it a try with the employee list in column G but the totals arent accurate.


Thanks for the help

[Ken Puls]

Hi there,

I've attached a sample workbook. Tell me if that's what you were after.

[Bob Phillips]

You could use these two array formulae

=IF(ISERROR(SMALL(IF($B$2:$B$15<>5,ROW($A$2:$A$15)),ROW(A1))),"",
INDEX($A$2:$A$15,SMALL(IF($B$2:$B$15<>5,ROW($A$2:$A$15)),ROW(A1))-1))

and

=IF(ISERROR(SMALL(IF($B$2:$B$15<>5,ROW($A$2:$A$15)),ROW(A1))),"",
INDEX($C$2:$C$15,SMALL(IF($B$2:$B$15<>5,ROW($A$2:$A$15)),ROW(A1))-1))

The first gets the name where week <> 5, and the second gets the total. Copy down as far as you think you might need.