Thread: SUMPRODUCT
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
M&M[_3_] M&M[_3_] is offline
external usenet poster
 
Posts: 5
Default SUMPRODUCT

The Min column is the calculation of MIN(ABS(A1:C1)) by row. Now I need to
have a count by each column that the value is equal to the Min column. I
tried to use the sumproduct as below but it doesn't work:

=sumproduct(--(A1:A9<"?"),ABS(A1:A9)=(D1:D9))

My data is randomly to have "?"; therefore I need to make sure the formula
do not count the "?" and also treat all valuse as "ABS". The results should
be 3 in col A, 4 in col B, and 5 in col C.

Any hlep is greatly appreciated?

A B C MIN
1 -14% -14% -11% 11%
2 -12% -9% 9% 9%
3 -16% -16% -19% 16%
4 -13% -12% 12% 12%
5 -16% -17% -16% 16%
6 -16% -14% -10% 10%
7 ? ? ? ?
8 -17% -17% -18% 17%
9 -19% -17% -18% 17%