0

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

Improve this question

1 Answer 1

Reset to default
3

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))
Improve this answer
3
  • 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, 2014 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, 2014 at 13:20
  • @CharlesH Only VBA will pull from a closed workbook.
    – Raystafarian
    Jul 8, 2014 at 14:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .