Simple IF breaks after first match

bbushue

New Member
Joined
Feb 19, 2004
Messages
2
I'm attempting to execute a simple calculation when the value of a cell on my report sheet is found in a one-column range on a rawdata sheet. The other sheet includes many duplicates. The formula works on the first instance of a match, but subsequent matches fail.

With the help of CPearson (=IF(COUNTIF(Details!$A$1:A2,Details!A2)=1,Details!A2,""), i've tried building of list of unique values from which to execute my matching criteria...but, alas, no success.

Here's the detail.

=IF(Details!$A$2:$A$304=A:A,(SUM(MiniTables!$L$3:$L$7)/MiniTables!$M$3),"")

Details!$A$2:$A$304 is a range on another sheet with 250+ values, only 10 of which are unique. About 10 cells are blank.
A:A is a full list of real estate properties in a portfolio. I only want to report on them if there is activity as indicated by the "details!" sheet. If there is no match, i leave the cell blank. If there is a match, i do a simple calculation:
Sum a range of cells (L3:L7) and then devide it by a number (M3).

I have no hair left to lose on this one. A little help here?

bb
[/code]
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Create a scaled down sample (20 rows at most) that reflects the structure of your data and post it here along with desired results (no formulas) by means of HtmlMaker.
 

bbushue

New Member
Joined
Feb 19, 2004
Messages
2
Below is the REPORT sheet. The content of this sheet is refreshed daily and will grow to 10,000 rows.
WeeklyDashboard01.xls
ABCD
1RevenueYTD
2PropertyYTDOngoingCosts
3ADT
4ALX
5ANN
6AUH$601
7BAM
8BCH$601
9BLA$601
YTD
Code:

Below is the Raw Data Sheet (DETAILS)
WeeklyDashboard01.xls
ABCDE
1SiteCodepYearpMonthpDayDOW
2AUH2004119
3AUH20041202
4AUH20041213
5BCH20041272
6BCH20041294
7BCH2004130NULL
8BCH2004131NULL
9BCH200428NULL
10BLA2004127NULL
11BLA2004129NULL
12BLA2004130NULL
Details


Below is a slice of the MINITABLES Sheet from where the calculation is made. Ideally this is in a separate file, but i have cut and pasted into this workbook for the sake of trying to solve this problem...
WeeklyDashboard01.xls
KLMN
1ActualForecasted2005
2CapitalOngoing toEOMLivePropertiesOngoing
3$6,803$5,81418$366,405
4$123,083$0$50,000
5$15,898
6$7,249$5,000$150,000
7TBDTBD$65,000
MiniTables


Hope this helps.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,490
Messages
5,764,667
Members
425,228
Latest member
Rashid mahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top