![]() |
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. |
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. |
All times are GMT +1. The time now is 02:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com