# Excel Formula Issue Countif to Sumproduct

Having a nightmare at the moment with some formula's I've been asked to 'fix'. It would seem the users expected them to work with closed source spreadsheets but they don't. So they end up having to open about 10 spreadsheets to get all the values added in.

I've tried to figure out how to change from Countif to Sumproduct which seems to be the only resolution but with no such luck.

Hoping an Excel Guru out there can assist.

Formula as it stands now…:

=COUNTIFS('S:\Data\HQ\2014\Report.xlsx'!Table2[RFI Date],">="&B1,'S:\Data\HQ\2014\Report.xlsx'!Table2[RFI Date],"<="&B2)

Thanks

-

So you have

``````=COUNTIFS('S:\Data\HQ\2014\Report.xlsx'!Table2[RFI Date],">="&B1,'S:\Data\HQ\2014\Report.xlsx'!Table2[RFI Date],"<="&B2)
``````

Which is basically count how many rows in the table are both greater than or equal to `B1` AND less than or equal to `B2`. This would be the same as:

``````=SUMPRODUCT(('S:\Data\HQ\2014\Report.xlsx'!Table2[RFI Date]>=B1)*('S:\Data\HQ\2014\Report.xlsx'!Table2[RFI Date]<=B2)
``````

The `*` works as an `AND` in this formula, so it counts only if both conditions are met.

Or more simply, you can see what I did this way:

``````=COUNTIFS(C1:C7,">3",C1:C7,"<6")
``````

is the same as

``````=SUMPRODUCT((C1:C7>3)*(C1:C7<6))
``````
-
Thanks for explaining makes a lot more sense and is now working kind of… still goes back to #Ref! When I close the source spreadsheet though like it did with COUNTIFS any ideas? – CharlesH Jul 8 '14 at 13:13
Ok another forum says that SUMPRODUCT doesn't work on closed spreadsheets either… is there actually a solution for this or is pulling cells/info from closed spreadsheets just not possible? – CharlesH Jul 8 '14 at 13:20
@CharlesH Only VBA will pull from a closed workbook. – Raystafarian Jul 8 '14 at 14:29