Aladin or Robb or anyone again :)

RoB,

You don't need to enter the SUMPRODUCT formulas as array formulas.
What follows is more important: Array formulas and SUMPRODUCT formulas cannot have a whole column (e.g., A:A) as a range specification. When you don't know the exact range, because it changes regularly in size, you can do the following:
(1) Create the so-called named dynamic ranges.
(2) If you can keep the formulas in the same worksheet as the ranges it uses, you could use a UDF (called Used) that computes the ranges in a given column dynamically.

The second is not, I reckon, an option in your case. So, we will define named dynamic ranges. I'll do these for ranges in A and in B where you have dates and officers, respectively.

Activate DATE [ I thought it was DATA instead of DATE. Lost 1/2 because of this misreading. :( ]
Activate Insert|Name|Define.
Enter LastRow as name in the Names in Workbook box.
Enter the following in the Refers To box:

=MATCH(9.99999999999999E+307,DATE!$A:$A)

Activate Add (don't leave the Define Name window).

Enter also DATES as name in the Names in Workbook box.
Enter the following in the Refers To box:

=OFFSET(DATE!$A$2,0,0,LastRow,1)

Activate Add (don't leave the Define Name window).

Enter also OFFICERS as name in the Names in Workbook box.
Enter the following in the Refers To box:

=OFFSET(DATE!$B$2,0,0,LastRow,1)

Activate OK.

On Sheet1 (where you have your Weekly Credit Checks),

in B5 enter: =SUMPRODUCT((DATES=B$4)*(OFFICERS=$A5))

This formula (or an equivalent array formula) has a boolean arg [ * means AND ]. I suppose that's what makes them difficult to understand.
Maybe the following can tell you how it works:

Activate B5, go to the formula bar, select (DATES=B$4), and hit F9.
You'll see:

=SUMPRODUCT({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*(OFFICERS=$A5))

Select now (OFFICERS=$A5) and hit F9. You'll see:

=SUMPRODUCT({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*{TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE})

Select {..}*{...} and hit F9. You'll see:

=SUMPRODUCT({1;1;0;0;0;0;0;0;0;0;0;0})

Select the whole thing at this stage and hit F9. You'll see: 2.

It thus computed first 2 arrays of TRUEs and/or FALSEs. Multiplying these two arrays coerces Excel to treat TRUE values as 1's and FALSE values as 0's. Multiplication yields a single array consisting of 1s and/or 0's. Finally, it just "sums" what is in the resulting single array. A result that represents a multiconditional count.

I leave the macro question to you.

Aladin

PS. The workbook is underway to you.

========================

Another question...

Thanks for the explanation, it does really help. I understand the reasoning for making dynamic named ranges, but will there be a way to keep this updated if a user adds a sheet of data, and uses that data? or is this impossible? if i use "DATE!" in the named formulas, what if the user wants to put in data form "OLD DATE" sheet? I know I could name ranges for that too, but i need them to be created automatically so that users can update data without me modifying the named ranges everytime they need me to...Any ideas would be appreciated.

Thanks

Nevermind, thanks!

found a way to change the name of the dynamic ranges using my code :)

Re: Another question...

RoB,

You should restrict all updates (that is, additions [new data] and deletions [deleting rows]) to a single worksheet, that is, to DATE. The defined ranges then will be always corrrect.

Aladin

==============

Re: Nevermind, thanks!

How?

Re: Nevermind, thanks!

I ask the user for the name of the sheet they want to use, then use that name to change the dynamic ranges. Heres the code I'm using:

Sub SheetSelector()

Dim ChooseSheet As String
Dim CurrentSheet As String

ChooseSheet = InputBox("Enter the EXACT name of the sheet for this week")
CurrentSheet = ActiveSheet.Name

ActiveWorkbook.Names.Add Name:="DATES", RefersToR1C1:= _
"=OFFSET('" & ChooseSheet & "'!R2C1,0,0,COUNTA('" & ChooseSheet & "'!C1),1)"
ActiveWorkbook.Names.Add Name:="LOANOFFICER", RefersToR1C1:= _
"=OFFSET('" & ChooseSheet & "'!R2C2,0,0,COUNTA('" & ChooseSheet & "'!C2),1)"

Range("B5").Formula = "=SUMPRODUCT(('Dynamic Multiple Countif.xls'!DATES=B$4)*('Dynamic Multiple Countif.xls'!LOANOFFICER=$A5))"

Range("DATA_RANGE").Select
Selection.FillRight
Selection.FillDown

End Sub

Hmm...

it looks like you'll soon join the VBA army. :)

lol, thanks, i hope so