Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help based around countif and counta
I have a situation where there are 4 columns of rates from different
suppliers. The suppliers have only submitted rates for the locations they can do and these rates are listed by state. Those locations where they cannot provide the service have a zero in their field for those locations. The zeros have to remain to show that that they are zero and not just forgotten. What I need to do is to count the list of rates only for non-zero rates and do this by state. I know I could autofilter the data and count from that but I'm looking for something a little bit more sophisticated than that. The countif and counta functions should be involved here and exactly what is the difference between these two. Any help appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help based around countif and counta
Try something like this:
Assuming you have a data structure like this: StateList Supplier1 Supplier2 Supplier3 Supplier4 State1 1 0 3 1 State2 0 2 3 1 State3 1 0 0 1 The count of Supplier1 non-zero rates would be: =COUNTIF(B:B,"0") Supplier2 would be: =COUNTIF(C:C,"0") etc -------------- The COUNTIF functions counts items that match a user defined critera. The COUNTA function counts non-blank cells....check Excel Help for more information on what that means. Does that help? *********** Regards, Ron XL2002, WinXP "Mark McDonough" wrote: I have a situation where there are 4 columns of rates from different suppliers. The suppliers have only submitted rates for the locations they can do and these rates are listed by state. Those locations where they cannot provide the service have a zero in their field for those locations. The zeros have to remain to show that that they are zero and not just forgotten. What I need to do is to count the list of rates only for non-zero rates and do this by state. I know I could autofilter the data and count from that but I'm looking for something a little bit more sophisticated than that. The countif and counta functions should be involved here and exactly what is the difference between these two. Any help appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF formula multiple text exclusions | Excel Discussion (Misc queries) | |||
countif counta with multiple lookup criteria | Excel Discussion (Misc queries) | |||
Errors in COUNT, COUNTA, COUNTIF when counting merged cells | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
COUNTA, COUNTIF? | Excel Worksheet Functions |