Count the records basing 2 different cells

**rahulguha** · 10-23-2009, 06:25 AM

Question
I have a simple workbook with 5 spreadsheets and I need to get Excel 2007 to count the number of closed records on spreadsheet 4 based on date where

'Week 4'!I7:I66 is Date
AND
'Week 4'!G7:G66 = Closed

I should have been able to figure this out, but I am stumped

.

Thanks,
Rahul

**DonkeyOte** · 10-23-2009, 06:31 AM

Given use of XL2007

=COUNTIFS('Week 4'!I7:I166,your date,'Week 4'!G7:G66,"Closed")

**rahulguha** · 10-23-2009, 06:44 AM

Thanks for the quick reply....but it didnt work. I tried

=COUNTIFS('Week 4'!I7:I67,22-Oct,'Week 4'!G7:G66,"Closed")

it gave me #VALUE!. Can you help me please..

thank you
Rahul

**DonkeyOte** · 10-23-2009, 06:51 AM

Firstly, ensure the two ranges are of equal size

Second, you would need to pass the date criteria slightly differently, ie:

=COUNTIFS('Week 4'!I7:I67,"22-Oct-09",'Week 4'!G7:G67,"Closed")

**rahulguha** · 10-23-2009, 07:02 AM

YES...that worked....thank you...
one more doubt in this....

the solution is for a single spredsheet.....how can i count the same from 2-3 spreadsheets in same workbook

**rahulguha** · 10-23-2009, 07:09 AM

Never mind....found the answer....thank you for your help Sir....

**DonkeyOte** · 10-23-2009, 07:10 AM

If you have only 2-3 sheets it is better to use 3 COUNTIFS and sum them, ie:

=SUM(COUNTIFS('Week 2'!I7:I67,"22-Oct-09",'Week 2'!G7:G67,"Closed"),COUNTIFS('Week 3'!I7:I67,"22-Oct-09",'Week 3'!G7:G67,"Closed"),COUNTIFS('Week 4'!I7:I67,"22-Oct-09",'Week 4'!G7:G67,"Closed"))

"elegant" formulae in terms of 3D conditional summation are not efficient.