Most efficient formula/combining multiple data cell ranges/seperat
 

I have 2 wksts', same wkbook..
wkst#1=Summary. Open
wkst#2=Holds Open. Current
On w#2 I have 10 columns: 5= Current Reasons ; 5= Current Owner
All rows for all columns have an existing formula,i.e. =+IF($K2,"Resolution
Pending",1,0) for the Current Reasons columns & =+IF($N2,"Nick Mileti",1,0)
for the Current Owners columns. Now, on w#1, I have the 5 names listed
vertically, in 1 column,5 rows & the 5 reasons are listed horizontally, on 1
row, 5 columns..

Resolution Pending RTA Masters Archives
Temple
Nick
Ida
Amanda
Rose
Sharon

I need Excel to combine,match, which reason belongs to who
from w#2 onto w#1 . So, =COUNTIF('Holds Open.
Current'!$K$2:$K$166,"Resolution Pending")*(COUNTIF('Holds Open.
Current'!$N$2:$N$166,"NickMileti")) is not working! I've tried
AVG,IF,SUMPRODUCT,etc.,.... still not working! Please Help!!

Thanks,
Tiffany

Most efficient formula/combining multiple data cell ranges/seperat
 

You may use SUMPRODUCT this way:
=SUMPRODUCT(--('Holds Open. Current'!$K$2:$K$166="Resolution
Pending"),--('Holds Open. Current'!$N$2:$N$166="NickMileti"))

Hope this helps,
Miguel.

"Tiff" wrote:

I have 2 wksts', same wkbook..
wkst#1=Summary. Open
wkst#2=Holds Open. Current
On w#2 I have 10 columns: 5= Current Reasons ; 5= Current Owner
All rows for all columns have an existing formula,i.e. =+IF($K2,"Resolution
Pending",1,0) for the Current Reasons columns & =+IF($N2,"Nick Mileti",1,0)
for the Current Owners columns. Now, on w#1, I have the 5 names listed
vertically, in 1 column,5 rows & the 5 reasons are listed horizontally, on 1
row, 5 columns..

Resolution Pending RTA Masters Archives
Temple
Nick
Ida
Amanda
Rose
Sharon

I need Excel to combine,match, which reason belongs to who
from w#2 onto w#1 . So, =COUNTIF('Holds Open.
Current'!$K$2:$K$166,"Resolution Pending")*(COUNTIF('Holds Open.
Current'!$N$2:$N$166,"NickMileti")) is not working! I've tried
AVG,IF,SUMPRODUCT,etc.,.... still not working! Please Help!!

Thanks,
Tiffany