MS EXCEL: Counting based on multiple columns

**Revati** · 02-11-2010

I have the Microsoft Office 2003 and I want to count the number of rows here. It is not like that I do not know how to count the rows but I want to have the rows counted when there are multiple columns. In pseudo terms I want to count rows such that Column A has some value and Column B has something like *@*. I want to have the entire number of rows if column A has a precise value and column B matches a prototype. I think that I have explained the scenario and I will be happy if you can explain me the way of doing this. Please suggest me a way so that I can get the things in the manner that I want. Please be precised and give me something useful. Any help in the matter will be greatly appreciated.

**Shantimay** · 02-11-2010

Well it is very simple and I do not know how you missed on the same. Well in the Microsoft Excel there is function called COUNTIFS, and I am of the opinion that this function is the only thing that can help you to get out of the current situation of yours. It actually counts up the number of times data in two or more series of cells congregate multiple criteria. The only thing that you will have to care about is that the ranges must be of the identical size. Also the function will count occasions where the condition for each range is met concurrently. If at all you want you can have the syntax of the same as follows:

=COUNTIFS( Criteria_range-1, Criteria-1, Criteria_range-2, Criteria-2, ...)

See if that can help you in any manner.

**Revati** · 02-11-2010

Thanks a lot for the reply but the thing is that I was aware of the COUNTIFS function and the only thing is that it is not in the MS 2003. Actually the MS 2003 Excel version of the Microsoft simply lacks it and hence can not be used. Well it is present in the 2007 version and it can be used there. I was having the MS 2007 and the thing is that I have just formatted and the system and then when I searched for the MS 2007 CD I was unable to find the same, so I thought of using the MS 2003 and then got stuck with the problem. Is there any other way by which I can use the same function on the MS 2003. Also is there any other function that I can use so that it will serve m e in the same manner as that COUNTIFS would have.

**Sam.D** · 02-11-2010

I was about to say the same thing regarding the COUNTIFS function that it is completely absent in the Microsoft Office 2003 and I am sorry to say that there is no other way by which you can have this function on the MS 2003. If it would have been something that was supported by 2003 and not by 2007 then Microsoft would have released some updates and that would have solved the issue but here the flow is in the other direction. Regarding the other function I think I will have to search regarding the same on the net and if I find anything I promise to post as soon as possible. Till then you can wait for the other members to turn up and see what they have to say regarding the same.

**Tejapaala** · 03-11-2010

I have the Microsoft Office 2007 and the thing is that I do not have any issue with the same. I was successfully able to apply the COUNTIFS function and I was able to get the thing done. My problem is that now I want to save the Microsoft Office 2007 Excel sheet with the COUNTIFS in the Microsoft Office 2003 format. Well I am very much aware that the Microsoft Office 2003 does not support the COUNTIFS function and the fact is that I have been challenged for the same and I want to have the solution any thing in the form of alternative will also do. Here is the thing:

=COUNTIFS(Data3!$A:$A,$A22,Data3!$C:$C,"=wb*")

Any sort of suggestion will be really appreciated. Thanks in advanced.

**Raine** · 04-11-2010

If you want to have the easiest way then I think I have a better way by which you will be able to get the thing done. Just follow the steps below and I am sure that you will be able to tackle the problem. Just insert a new column and that too in the beginning or at the end of the spreadsheet. Just place it near the column that you are willing to count. After that you will have to insert an IF statement in the new column and then. In the condition you will have to put a criteria if that column satisfies the criteria then give 1 to it and if it does not satisfies the criteria then you will be required to give the value 0. The column that you have created will be dynamic and that can be hidden on the wish. It is going to act like a reference column. This is the manner in which I would have tackled the situation. If that works well for you and it is nice but if it does not then I think that you can also try your hand on the VBA code instead

**Eric B** · 06-11-2010

If at all you are looking at the alternative for the COUNTIFS function then I think I have the best alternative and I hope that it will serve you in the same manner. Here it is the name of the other function is DCOUNTA. The SYNTAX for the same is as follow:

DCOUNTA (database,field,criteria)

Where the database is the range of cells that makes up the list or database.
field is used to point which column is utilized in the function.
criteria is the range of cells that surround the conditions that is being specified.
In the above syntax the field argument is not obligatory. If this particular field is absent, DCOUNTA will count the entire records that are present in the database that match the criterion.