Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculations
I am attempting to create a function to do the following.
I have to columns of data that I need to go through. I am attempting to talley the number of occurences of each item. To do this I need to go through column 'a' and every time it is 1, i then must check column 'b'. If in column b the numbers 1,3,5,6 are found then I need to add one to the talley. Can anyone suggest a way to do this. I attempted to use countif formula but could not get something on it to work. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculations
Hi!
Try this: =SUMPRODUCT(--(A1:A20=1),--(ISNUMBER(MATCH(B1:B20,{1,3,5,6},0)))) Biff "J. McGonigal" wrote in message ... I am attempting to create a function to do the following. I have to columns of data that I need to go through. I am attempting to talley the number of occurences of each item. To do this I need to go through column 'a' and every time it is 1, i then must check column 'b'. If in column b the numbers 1,3,5,6 are found then I need to add one to the talley. Can anyone suggest a way to do this. I attempted to use countif formula but could not get something on it to work. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculations
The other option is to create a Pivot Table. Once you understand them, they are
very slick and very powerful. There are several sites, including www.cpearson.com which explain Pivot Tables. -- Regards, Fred "J. McGonigal" wrote in message ... I am attempting to create a function to do the following. I have to columns of data that I need to go through. I am attempting to talley the number of occurences of each item. To do this I need to go through column 'a' and every time it is 1, i then must check column 'b'. If in column b the numbers 1,3,5,6 are found then I need to add one to the talley. Can anyone suggest a way to do this. I attempted to use countif formula but could not get something on it to work. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculations
Thanks, this worked wonderfully! Would mine explaining what the "--" do?
"Biff" wrote: Hi! Try this: =SUMPRODUCT(--(A1:A20=1),--(ISNUMBER(MATCH(B1:B20,{1,3,5,6},0)))) Biff "J. McGonigal" wrote in message ... I am attempting to create a function to do the following. I have to columns of data that I need to go through. I am attempting to talley the number of occurences of each item. To do this I need to go through column 'a' and every time it is 1, i then must check column 'b'. If in column b the numbers 1,3,5,6 are found then I need to add one to the talley. Can anyone suggest a way to do this. I attempted to use countif formula but could not get something on it to work. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculations
Thanks, I will begin to look at this in the near future.
"Fred Smith" wrote: The other option is to create a Pivot Table. Once you understand them, they are very slick and very powerful. There are several sites, including www.cpearson.com which explain Pivot Tables. -- Regards, Fred "J. McGonigal" wrote in message ... I am attempting to create a function to do the following. I have to columns of data that I need to go through. I am attempting to talley the number of occurences of each item. To do this I need to go through column 'a' and every time it is 1, i then must check column 'b'. If in column b the numbers 1,3,5,6 are found then I need to add one to the talley. Can anyone suggest a way to do this. I attempted to use countif formula but could not get something on it to work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculations in French - Clarification | Excel Discussion (Misc queries) | |||
complex calculations | Excel Discussion (Misc queries) | |||
Calculations based on PivotTable information | Excel Discussion (Misc queries) | |||
looping through a set of calculations | Excel Worksheet Functions | |||
Calculations crossing multiple sheets | Excel Discussion (Misc queries) |