COUNTIFS a cell in a range is greater than another cell in the sames column. I h
ID: 3563341 • Letter: C
Question
COUNTIFS a cell in a range is greater than another cell in the sames column.
I have been searching and searching through search engines for what I am sure is a relativley simple referencing mishap on my part.
I have a set of data for a sales team: Column A has the sales persons name, column J has week 1's (FW1) sales as a comp % (% variance to last year), column K week 2 (FW2), Column L = week 3 (FW3), column M = Week 4 and so forth up to week 52. There are also Period 1, Period 2, period 3 columns after the corresponding weeks and quarter columns after the corresponding periods. The range goes out to Column BW
Lets say A2 to A9 have different sales peoples names and A10 is the regional average. I am trying to figure out how to use countifs to show me how many times the sales person has been over the corresponding week's regional average.
Being a trend report, data is added weekly so I have the formula looking for
A) What columns are titled as a Week (excludes periods and quarters)
B) What columns are not blank (excludes Weeks that have not happened yet)
and what I am missing is:
C) If the sales persons result (variance to LY) is higher than that of the region.
Here is the formula I have so far that is returning a "0":
=COUNTIFS($J$1:$BW$1,"=FW*",$J2:$BW2,"<>"&"",$J2:$BW2,">"&"'""10")
Here is an example:
Name FW1 FW2 FW3 FW4
John 3% 4% 2% -4%
.........
.........
Region 2% 3% 4% -1%
The result should be [2] times that John did better than the regional average.
Explanation / Answer
Try this
=SUMPRODUCT(--(J2:M2>$J$9:$M$9))
J9 to M9 are the region numbers.
or to check for FW in row 1 as well
=SUMPRODUCT(--(J2:M2>$J$9:$M$9)*(LEFT($J$1:$M$1,2)="FW"))