# replacing countifs for excel 2003

1. ## replacing countifs for excel 2003

hi, i currently have a working forumla for excel 2007 which is =COUNTIFS(\$H\$2:\$H\$4000, ">="&\$P\$2,\$H\$2:\$H\$4000,"<="&\$P\$3,\$E\$2:\$E\$4000,"=Dog") however i am trying to adapt it for someone who only has access to excel 2003. please could someone help me with this??

The cells in column E is a drop down list containing options such as 'dog' 'cat' etc.
Cell P2 contains the start date of the spreadsheet
Column H contains dates which the user enters.
The overall result only counts entries which have both Dog written in column E and a valid date entered in column H.
Please can someone help me with this??

Many thanks,

2. ## Re: replacing countifs for excel 2003

use sumproduct
=sumproduct((\$H\$2:\$H\$4000">="&\$P\$2)*(\$H\$2:\$H\$4000"<="&\$P\$3)*(\$E\$2:\$E\$4000"=Dog")*(1))

Regards,
Suhas

3. ## Re: replacing countifs for excel 2003

Formula:
`Please Login or Register  to view this content.`

4. ## Re: replacing countifs for excel 2003

Thanks for the reply,
i have pasted the forumla into the spreadsheet, however it is still not working. the first \$H\$4000'' seems to be highlighted as the problem?
What is the (1) for at the end btw?

Thanks,

5. ## Re: replacing countifs for excel 2003

Did you checked post#3??

6. ## Re: replacing countifs for excel 2003

It's the " that is the problem Look again at the suggestions offered.

7. ## Re: replacing countifs for excel 2003

yep, just saw post #3 - worked perfectly thank you!!
i also have another slight problem, for one of the cells i need it to count if 1) a valid date is entered and 2) either 'dog' or 'cat' are entered. At the moment i have =COUNTIFS(\$H\$2:\$H\$4000, ">="&\$P\$2,\$H\$2:\$H\$4000,"<="&\$P\$3,\$E\$2:\$E\$4000,"=Cat",\$E\$2:\$E\$4000,"=Dog") as my forumla however it doesnt seem to pick up when Dog is entered (only when cat is entered), please can someone help with both 2007 and 2003 versions of excel?

Many thanks,

8. ## Re: replacing countifs for excel 2003

For 2007+:

=SUM(COUNTIFS(\$H\$2:\$H\$4000, ">="&\$P\$2,\$H\$2:\$H\$4000,"<="&\$P\$3,\$E\$2:\$E\$4000,{"Cat","Dog"}))

for 2003 or earlier:
=SUMPRODUCT((\$H\$2:\$H\$4000>=\$P\$2)*(\$H\$2:\$H\$4000<=\$P\$3)*(\$E\$2:\$E\$4000={"Cat","Dog"}))

9. ## Re: replacing countifs for excel 2003

thank you romperstomper, still cant get it to work though :/ do you have any other suggestions please?

10. ## Re: replacing countifs for excel 2003

scrap the above post - works perfectly, was an error on my behalf! many thanks all for your help, i know where to come if i have any furhter problems!

11. ## Re: replacing countifs for excel 2003

If you wish to thank someone here, you can do so by adding to their reputation ( the * icon below their user name).

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1