| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting 2 different pieces of data in 2 different columns
Hi -
This should be so simple - but I can't figure it out I need to do a countif in one column to count, for example, # of "auto quotes" and in another column count the number of "sold" that corresponds to the first column. Ideally, I could figure the percentage along with it, but if not, I can always do that in a separate cell. Thanks for any help! Cindyt |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting 2 different pieces of data in 2 different columns
If I understand what you want...
=SUMPRODUCT(--(A1:A10="auto quote"),--(B1:B10="sold")) Better to use cells to hold the criteria... D1 = auto quote E1 = sold =SUMPRODUCT(--(A1:A10=D1),--(B1:B10=E1)) -- Biff Microsoft Excel MVP "Cindyt" wrote in message ... Hi - This should be so simple - but I can't figure it out I need to do a countif in one column to count, for example, # of "auto quotes" and in another column count the number of "sold" that corresponds to the first column. Ideally, I could figure the percentage along with it, but if not, I can always do that in a separate cell. Thanks for any help! Cindyt |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting 2 different pieces of data in 2 different columns
Thanks - worked great!
"T. Valko" wrote: If I understand what you want... =SUMPRODUCT(--(A1:A10="auto quote"),--(B1:B10="sold")) Better to use cells to hold the criteria... D1 = auto quote E1 = sold =SUMPRODUCT(--(A1:A10=D1),--(B1:B10=E1)) -- Biff Microsoft Excel MVP "Cindyt" wrote in message ... Hi - This should be so simple - but I can't figure it out I need to do a countif in one column to count, for example, # of "auto quotes" and in another column count the number of "sold" that corresponds to the first column. Ideally, I could figure the percentage along with it, but if not, I can always do that in a separate cell. Thanks for any help! Cindyt |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting 2 different pieces of data in 2 different columns
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Cindyt" wrote in message ... Thanks - worked great! "T. Valko" wrote: If I understand what you want... =SUMPRODUCT(--(A1:A10="auto quote"),--(B1:B10="sold")) Better to use cells to hold the criteria... D1 = auto quote E1 = sold =SUMPRODUCT(--(A1:A10=D1),--(B1:B10=E1)) -- Biff Microsoft Excel MVP "Cindyt" wrote in message ... Hi - This should be so simple - but I can't figure it out I need to do a countif in one column to count, for example, # of "auto quotes" and in another column count the number of "sold" that corresponds to the first column. Ideally, I could figure the percentage along with it, but if not, I can always do that in a separate cell. Thanks for any help! Cindyt |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting 2 different pieces of data in 2 different columns
I try t he same thing but it doesn't work and gives me #NUM!
I want to count the entries from that satifies bothe conditions b1 from column B and c1 from column c Thanks "Cindyt" wrote: Thanks - worked great! "T. Valko" wrote: If I understand what you want... =SUMPRODUCT(--(A1:A10="auto quote"),--(B1:B10="sold")) Better to use cells to hold the criteria... D1 = auto quote E1 = sold =SUMPRODUCT(--(A1:A10=D1),--(B1:B10=E1)) -- Biff Microsoft Excel MVP "Cindyt" wrote in message ... Hi - This should be so simple - but I can't figure it out I need to do a countif in one column to count, for example, # of "auto quotes" and in another column count the number of "sold" that corresponds to the first column. Ideally, I could figure the percentage along with it, but if not, I can always do that in a separate cell. Thanks for any help! Cindyt |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting 2 different pieces of data in 2 different columns
Ok, there are two possible causes for the #NUM! error.
1. You already have #NUM! errors in one or both ranges 2. You're trying to use entire columns as range references and you're not usng Excel 2007. If you're not using Excel 2007 you can't do something like this: =SUMPRODUCT(--(A:A="x"),--(B:B="y")) Use smaller specific ranges like: =SUMPRODUCT(--(A1:A10="x"),--(B1:B10="y")) If you absolutely need to reference the whole column you can reference the whole column minus one cell. Like this: =SUMPRODUCT(--(A1:A65535="x"),--(B1:B65535="y")) Or =SUMPRODUCT(--(A2:A65536="x"),--(B2:B65536="y")) -- Biff Microsoft Excel MVP "C00l" wrote in message ... I try t he same thing but it doesn't work and gives me #NUM! I want to count the entries from that satifies bothe conditions b1 from column B and c1 from column c Thanks "Cindyt" wrote: Thanks - worked great! "T. Valko" wrote: If I understand what you want... =SUMPRODUCT(--(A1:A10="auto quote"),--(B1:B10="sold")) Better to use cells to hold the criteria... D1 = auto quote E1 = sold =SUMPRODUCT(--(A1:A10=D1),--(B1:B10=E1)) -- Biff Microsoft Excel MVP "Cindyt" wrote in message ... Hi - This should be so simple - but I can't figure it out I need to do a countif in one column to count, for example, # of "auto quotes" and in another column count the number of "sold" that corresponds to the first column. Ideally, I could figure the percentage along with it, but if not, I can always do that in a separate cell. Thanks for any help! Cindyt |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count pieces of records meet conditions in different columns | Excel Worksheet Functions | |||
| Lookup Multiple Pieces of Data... | Excel Discussion (Misc queries) | |||
| 70 WORKBOOKS - NEED PIECES OF DATA FROM EACH TO FORM LIST | Excel Discussion (Misc queries) | |||
| Charts with 2 different pieces of data and a displayed percentage difference | Excel Discussion (Misc queries) | |||
| combining 2 pieces of text from 2 columns | Excel Worksheet Functions | |||
Linear Mode
