Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Counting data
Hi
I really hope someone can help me this time, I haven't had much luck in the other forums. Basically I am working on a spreadsheet which has 2 columns, one is the priority column and the other is the Action column. At the end of the spreadsheet there is a table which totals up the priorities and the actions. I need the table to reflect the priorites against the action. The priorites are up to 1 month, up to 6 month and up to 1 year. The actions are Estates, Student Services and Dean of faculty. So how do i create a formula which says that the priority of 1 month is allocated to estates 4 times. I have tried =COUNTIF($G$4:$H$200,"="&$M5), but that just counts how many times up to 1 month features in the spreadsheet. I probably need some sort of IF statement, but i am not sure what? Can someone please help?? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting data
Hi,
You could try an array formula. This allows you to count, or sum, based on more than one criteria. For example: =sum(if($A$2:$A$200="up to 1 month",if($B$2:$B$200="Estates",1,0))) Press ctrl & shift when you have entered all the data to tell Excel you want it to be an array forumla. You will know if it has worked because it will put braces ( { } ) around the function in the formula bar. This array function simply performs the function for each cell in the array and adds one to the total if the conditions are met. I would also recommend including the cell references of your critera instead of putting them directly in the function as I have above. Hope this helps. ryadav wrote: Hi I really hope someone can help me this time, I haven't had much luck in the other forums. Basically I am working on a spreadsheet which has 2 columns, one is the priority column and the other is the Action column. At the end of the spreadsheet there is a table which totals up the priorities and the actions. I need the table to reflect the priorites against the action. The priorites are up to 1 month, up to 6 month and up to 1 year. The actions are Estates, Student Services and Dean of faculty. So how do i create a formula which says that the priority of 1 month is allocated to estates 4 times. I have tried =COUNTIF($G$4:$H$200,"="&$M5), but that just counts how many times up to 1 month features in the spreadsheet. I probably need some sort of IF statement, but i am not sure what? Can someone please help?? Thanks -- ryadav |
#3
|
|||
|
|||
Hi
Just to say thankyou very much, I tried what you suggested and it works. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro question | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel |