Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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))
share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.