Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2
Default CountIF on two criteria

I'm trying to tabulate a survey using Office 97. ONE of the things I
need to tabulate is dependent on two critera. If the first column is
"O" (a text field, as the other entires are ranges), then I do not
want to count the value for the second field. Otherwise, I want a
total of all the fields in a second column with a given value where
the value in the first column is something other than "O".

To make it more complicated, this was exported from an access database
from a multiple-choice survey. The other values are things like "1-2"
"3-4", and "5+". They are not numeric as far as the computer is
concerned.

I have Office 2003 available, but I really don't want to use it if it
possible to do it in Office 97. I am going to need to have a
PowerPoint that can be opened on an old laptop running Windows 98, and
it would be nice to be able to open everything on that laptop instead
of just the PowerPoint. These results are going to be put into a Word
document as well.

If there is a way to get access to give me the total number of times
each survey question answer comes up instead, I'd appreciate it if
somebody told me. The multiple choice responses are in a lookup text
filed in the Access97 database. I just can't recall how to use Access
very well as I haven't done this since Office97 was current, although
they were talking about the upcoming Office2000.
  #2   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 4,393
Default CountIF on two criteria

=SUMPRODUCT(--(a1:a100<"O"),--(B1:b100="CAT"),C1:C100)

This will sum (total) every C value whose corresponding B value is CAT and
corresponding A value is not O

=SUMPRODUCT(--(a1:a100<"O"),--(B1:b100="CAT"))
This will count how many B values are CAT with corresponding A values that
are not O

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctio...tml#SumProduct


best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"SlickRCBD" wrote in message
...
I'm trying to tabulate a survey using Office 97. ONE of the things I
need to tabulate is dependent on two critera. If the first column is
"O" (a text field, as the other entires are ranges), then I do not
want to count the value for the second field. Otherwise, I want a
total of all the fields in a second column with a given value where
the value in the first column is something other than "O".

To make it more complicated, this was exported from an access database
from a multiple-choice survey. The other values are things like "1-2"
"3-4", and "5+". They are not numeric as far as the computer is
concerned.

I have Office 2003 available, but I really don't want to use it if it
possible to do it in Office 97. I am going to need to have a
PowerPoint that can be opened on an old laptop running Windows 98, and
it would be nice to be able to open everything on that laptop instead
of just the PowerPoint. These results are going to be put into a Word
document as well.

If there is a way to get access to give me the total number of times
each survey question answer comes up instead, I'd appreciate it if
somebody told me. The multiple choice responses are in a lookup text
filed in the Access97 database. I just can't recall how to use Access
very well as I haven't done this since Office97 was current, although
they were talking about the upcoming Office2000.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
COUNTIF criteria bikerhal Excel Worksheet Functions 2 April 15th 08 08:07 PM
countif criteria Chris Morley Excel Worksheet Functions 6 June 17th 06 01:27 PM
Countif using format criteria not number criteria? Rumbla76 Excel Worksheet Functions 1 April 20th 05 05:38 AM
Countif using format criteria....not number criteria? Troy Excel Worksheet Functions 1 April 20th 05 04:50 AM
countif criteria johnT Excel Worksheet Functions 5 March 28th 05 02:55 AM


All times are GMT +1. The time now is 04:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"