Excel 2003 Countifs formula help please

1. Excel 2003 Countifs formula help please

hi all

i am currently using excell 2011 at home however 2003 verison at work, i have done a spreadsheet that checks a column on the other sheet for a letter and then in tern counts up another column. the formula is =COUNTIFS('3 RIFLES DVR DATABASE'!\$E\$10:\$E\$528,"A",'3 RIFLES DVR DATABASE'!\$I\$10:\$I\$528,"<>")

however when i open the spreasheet up in 2003 version i get a #NAME? error. does anyone have an idea on how i change the formula to get it to work or have a different formula i can try

thank you

mick

2. Re: Excel 2003 Countifs formula help please

=SUMPRODUCT(--('3 RIFLES DVR DATABASE'!\$E\$10:\$E\$528 = "A"),--('3 RIFLES DVR DATABASE'!\$I\$10:\$I\$528 ?????))

you have "<>" does not equal - what

change the formula in 2011 at home and see that it works , and then 2003 should be ok

3. Re: Excel 2003 Countifs formula help please

Hello mick
Can you attach a sample please, I have 2007 but I think a sumproduct may work
Peter

4. Re: Excel 2003 Countifs formula help please

If I remeber correctly countifs and sumif were only introduced in Excel 2007 and they were not made compatible with Excel 2003
i.e. Excel 2003 does not support those - only their single condition counterparts

5. Re: Excel 2003 Countifs formula help please

Too slow sorry
Peter

6. Re: Excel 2003 Countifs formula help please

hi mick, try:
=SUMPRODUCT(--('3 RIFLES DVR DATABASE'!\$E\$10:\$E\$528="A"),--('3 RIFLES DVR DATABASE'!\$I\$10:\$I\$528<>""))

Originally Posted by etaf
you have "<>" does not equal - what
that in COUNTIF means does not equals to blank.

7. Re: Excel 2003 Countifs formula help please

hi guys

etaf

the "<>" was used so it would count up anything in that range.

guys please find attached a the worksheet

in the workings sheet you will see countifs works but not in 2003 i get #Name? error

i have had to delete alot of sheets to be able to upload it on here, but you still have main sheet,workings and A Coy tab

8. Re: Excel 2003 Countifs formula help please

Ben, Gents

Sumproduct worked a treat, yet again thank you so much for everyone that took the time to take a look and help with this post.

great site, great people

mick

9. Re: Excel 2003 Countifs formula help please

thanks for clearing the "<>" up

10. Re: Excel 2003 Countifs formula help please

=SUMPRODUCT(--('3 RIFLES DVR DATABASE'!\$E\$10:\$E\$528="A"),--('3 RIFLES DVR DATABASE'!\$I\$10:\$I\$528<>""))

is it possible in sum product to check three columns? so column B as well as E and I

mick

11. Re: Excel 2003 Countifs formula help please

You can keep adding conditions in that same format, so if you only want to include rows where column B = "x" change to this version

=SUMPRODUCT(--('3 RIFLES DVR DATABASE'!\$E\$10:\$E\$528="A"),--('3 RIFLES DVR DATABASE'!\$I\$10:\$I\$528<>""),--('3 RIFLES DVR DATABASE'!\$B\$10:\$B\$528="x"))

12. Re: Excel 2003 Countifs formula help please

thanks mate, think this post is totally solved

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