Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / May 2008

Tip: Looking for answers? Try searching our database.

Converting COUNTIFS to 2003 format

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
JMVenhaus - 29 May 2008 13:39 GMT
I have the following function in an Excel 2007 workbook and it works well.

=COUNTIFS(Articles!E3:E55,"Quality of
Products",Articles!M3:M55,">0")+COUNTIFS(Articles!F3:F55,"Quality of
Products",Articles!M3:M55,">0")

How would I convert it to a format that would work in the 2003 version?

What I am trying to do is search the E and F columns for one of five topics
and count the number of times that it returns a positive score in the M
column.  Separately, I count the negative resoponses so that I can chart the
number of positives and negatives for each of the five topics.  If someone
has a better way to do this I would love to hear it.

Thanks
Ron Coderre - 29 May 2008 14:00 GMT
Try this:
=SUMPRODUCT((((Articles!E3:E55="Quality of Products")+
(Articles!F3:F55="Quality of Products"))>0)*(Articles!M3:M55>0))

Is that something you can work with?
Post back if you have more questions.

Regards,

Ron
Microsoft MVP - Excel

> I have the following function in an Excel 2007 workbook and it works well.
>
[quoted text clipped - 13 lines]
>
> Thanks
Teethless mama - 29 May 2008 14:16 GMT
SUMPRODUCT((Articles!E3:E55,"Quality of
Products")*(Articles!M3:M55,">0"))+SUMPRODUCT((Articles!F3:F55,"Quality of
Products")*(Articles!M3:M55,">0"))

> I have the following function in an Excel 2007 workbook and it works well.
>
[quoted text clipped - 11 lines]
>
> Thanks
Teethless mama - 29 May 2008 14:29 GMT
Ignored this one.

> SUMPRODUCT((Articles!E3:E55,"Quality of
> Products")*(Articles!M3:M55,">0"))+SUMPRODUCT((Articles!F3:F55,"Quality of
[quoted text clipped - 15 lines]
> >
> > Thanks
Teethless mama - 29 May 2008 14:27 GMT
or...

=SUMPRODUCT((Articles!E3:F55,"Quality of Products")*(Articles!M3:M55,>0))

> I have the following function in an Excel 2007 workbook and it works well.
>
[quoted text clipped - 11 lines]
>
> Thanks
Teethless mama - 29 May 2008 14:36 GMT
I need coffee
here is the correction...

=SUMPRODUCT((Articles!E3:F55="Quality of Products")*(Articles!M3:M55>0))

> or...
>
[quoted text clipped - 15 lines]
> >
> > Thanks
Ron Coderre - 29 May 2008 15:21 GMT
Re: =SUMPRODUCT((Articles!E3:F55="Quality of Products")*(Articles!M3:M55>0))

Don't know if this is an issue....but, if the cells in the
same row of Col_E and Col_F both contain "Quality of Products",
that formula double-counts the row.

Regards,

Ron
Microsoft MVP - Excel

> I need coffee
> here is the correction...
[quoted text clipped - 24 lines]
>> >
>> > Thanks
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.