| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF
Hi,
Guess I went to the wrong office discussion group (Access). Anyway, can someone assist please. I have in column L the words NEW & EXISTING & in column M the letters Y & N. What I need is to count how many cell that has NEW & Y (to be counted as one) & likewise same with EXISTING & Y. I've tried all sort of COUNTIF functions but it always return zero. Thanks. rgds, rudy |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF
Try these:
=SUMPRODUCT(--(L1:L100="new"),--(M1:M100="y")) =SUMPRODUCT(--(L1:L100="existing"),--(M1:M100="y")) Note that you can't use entire columns as range references with SUMPRODUCT (unless you're using Excel 2007). -- Biff Microsoft Excel MVP "Rudy" wrote in message ... Hi, Guess I went to the wrong office discussion group (Access). Anyway, can someone assist please. I have in column L the words NEW & EXISTING & in column M the letters Y & N. What I need is to count how many cell that has NEW & Y (to be counted as one) & likewise same with EXISTING & Y. I've tried all sort of COUNTIF functions but it always return zero. Thanks. rgds, rudy |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF
Hi,
It still returns zero. Don't know why. Below is the format. Just want to count how many has NEW & Y, EXISTING & Y. Thanks. L M NEW Y EXISTING N NEW N NEW Y NEW Y NEW N EXISTING Y EXISTING Y NEW N NEW Y "T. Valko" wrote: Try these: =SUMPRODUCT(--(L1:L100="new"),--(M1:M100="y")) =SUMPRODUCT(--(L1:L100="existing"),--(M1:M100="y")) Note that you can't use entire columns as range references with SUMPRODUCT (unless you're using Excel 2007). -- Biff Microsoft Excel MVP "Rudy" wrote in message ... Hi, Guess I went to the wrong office discussion group (Access). Anyway, can someone assist please. I have in column L the words NEW & EXISTING & in column M the letters Y & N. What I need is to count how many cell that has NEW & Y (to be counted as one) & likewise same with EXISTING & Y. I've tried all sort of COUNTIF functions but it always return zero. Thanks. rgds, rudy |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF
What is "it" that's returning zero? Show us the formula you are using so we
can help you. Biff's formula will work when used properly. Regards, Fred. "Rudy" wrote in message ... Hi, It still returns zero. Don't know why. Below is the format. Just want to count how many has NEW & Y, EXISTING & Y. Thanks. L M NEW Y EXISTING N NEW N NEW Y NEW Y NEW N EXISTING Y EXISTING Y NEW N NEW Y "T. Valko" wrote: Try these: =SUMPRODUCT(--(L1:L100="new"),--(M1:M100="y")) =SUMPRODUCT(--(L1:L100="existing"),--(M1:M100="y")) Note that you can't use entire columns as range references with SUMPRODUCT (unless you're using Excel 2007). -- Biff Microsoft Excel MVP "Rudy" wrote in message ... Hi, Guess I went to the wrong office discussion group (Access). Anyway, can someone assist please. I have in column L the words NEW & EXISTING & in column M the letters Y & N. What I need is to count how many cell that has NEW & Y (to be counted as one) & likewise same with EXISTING & Y. I've tried all sort of COUNTIF functions but it always return zero. Thanks. rgds, rudy |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF
Hi,
I've just copy & paste Biff's formula below on my spreadsheet, however, result still returns "0". rgds, rudy "Fred Smith" wrote: What is "it" that's returning zero? Show us the formula you are using so we can help you. Biff's formula will work when used properly. Regards, Fred. "Rudy" wrote in message ... Hi, It still returns zero. Don't know why. Below is the format. Just want to count how many has NEW & Y, EXISTING & Y. Thanks. L M NEW Y EXISTING N NEW N NEW Y NEW Y NEW N EXISTING Y EXISTING Y NEW N NEW Y "T. Valko" wrote: Try these: =SUMPRODUCT(--(L1:L100="new"),--(M1:M100="y")) =SUMPRODUCT(--(L1:L100="existing"),--(M1:M100="y")) Note that you can't use entire columns as range references with SUMPRODUCT (unless you're using Excel 2007). -- Biff Microsoft Excel MVP "Rudy" wrote in message ... Hi, Guess I went to the wrong office discussion group (Access). Anyway, can someone assist please. I have in column L the words NEW & EXISTING & in column M the letters Y & N. What I need is to count how many cell that has NEW & Y (to be counted as one) & likewise same with EXISTING & Y. I've tried all sort of COUNTIF functions but it always return zero. Thanks. rgds, rudy |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
COUNTIF
Check your cells with the data.
Make sure you don't have any extra spaces (leading or trailing) in cells in both ranges. Rudy wrote: Hi, I've just copy & paste Biff's formula below on my spreadsheet, however, result still returns "0". rgds, rudy "Fred Smith" wrote: What is "it" that's returning zero? Show us the formula you are using so we can help you. Biff's formula will work when used properly. Regards, Fred. "Rudy" wrote in message ... Hi, It still returns zero. Don't know why. Below is the format. Just want to count how many has NEW & Y, EXISTING & Y. Thanks. L M NEW Y EXISTING N NEW N NEW Y NEW Y NEW N EXISTING Y EXISTING Y NEW N NEW Y "T. Valko" wrote: Try these: =SUMPRODUCT(--(L1:L100="new"),--(M1:M100="y")) =SUMPRODUCT(--(L1:L100="existing"),--(M1:M100="y")) Note that you can't use entire columns as range references with SUMPRODUCT (unless you're using Excel 2007). -- Biff Microsoft Excel MVP "Rudy" wrote in message ... Hi, Guess I went to the wrong office discussion group (Access). Anyway, can someone assist please. I have in column L the words NEW & EXISTING & in column M the letters Y & N. What I need is to count how many cell that has NEW & Y (to be counted as one) & likewise same with EXISTING & Y. I've tried all sort of COUNTIF functions but it always return zero. Thanks. rgds, rudy -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
| edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
| countif max value help!!! | Excel Worksheet Functions | |||
| COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Hybrid Mode
