Array issue

[ddotson]

Hello everyone - here is my situation. I have one tab, which is a data dump, a second tab that has static information, as well as a vlookup to the data dump tab, and a third tab I am using for the summary...

I am trying to count the number of time something has been reviewed, which will show up as a date within the vlookup formula, but the cells can not be pasted as values - needs to keep the formula. With that said, I am having trouble counting, as there is always a value in the cell...

Here is my array formula:

=COUNT(IF(A3='NOI Data'!$B$6:$B$3670,IF('NOI Data'!$H$6:$H$3670="GNW Direct Serviced Loan",IF('NOI Data'!$U$6:$U$3670>0,(COUNT('NOI Data'!$U$6:$U$3670))))))

The Problem is that the U column is the product of the VLOOKUP...any suggestions would be very helpful.

Thanks!

 

[Aladin Akyurek]

Avoid using (non-working) formulas for describing a problem though...

Guessing...

Control+shift+enter, not just enter:

=SUM(
    IF('NOI Data'!$B$6:$B$3670=A3,
    IF('NOI Data'!$H$6:$H$3670="GNW Direct Serviced Loan",
    IF(1-ISNA('NOI Data'!$U$6:$U$3670),
      1))))

Is this close?

 

[ddotson]

No sir - but thank you....

 

[Aladin Akyurek]

No sir - but thank you....

Care to elaborate? If possible, try to post a tiny sample along with the desired count...

 

[ddotson]

The result of the formula was zero, and I know the result should be 32...

I am sorry - stupid my way - not sure how to paste the page information...

 

[Aladin Akyurek]

The result of the formula was zero, and I know the result should be 32...

I am sorry - stupid my way - not sure how to paste the page information...

=SUM(
    IF('NOI Data'!$B$6:$B$3670=A3,
    IF('NOI Data'!$H$6:$H$3670="GNW Direct Serviced Loan",
    IF(1-ISNA('NOI Data'!$U$6:$U$3670),
      1))))

The above formulas says:

If a B-cell is equal to A3, and if a H-cell houses a string like GNW Direct Serviced Loan, and if a U-cell does not house any #N/A, count the B/H/U record in, otherwise don't.