|
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 |
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 |
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 |
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 |
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 |
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 |
COUNTIF
Hi,
Confirm there's no extra spaces in both coulums. rgds, rudy "Dave Peterson" wrote: 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 |
COUNTIF
Rudy, you have two choices. You can choose to be a victim, or you can find
the solution. Right now, you are choosing the victim route. You want us to commiserate with you that it's not your fault you can't find a solution. But if you really want a solution, it won't be that hard to find. Check for: * Are your ranges correct? Is your data really in L1:L100 and M1:M100? * Is the underlying data correct? * What happens if you break down the process into steps? What happens if you check for just the Y and the N? What happens if you do a Countif against the data? The solution is there. You just need to be motivated to find it. Regards, Fred. "Rudy" wrote in message ... Hi, Confirm there's no extra spaces in both coulums. rgds, rudy "Dave Peterson" wrote: 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 |
COUNTIF
Maybe you have calculation set to manual???
You sure your data is in L1:M100? I'm pretty much out of ideas. Rudy wrote: Hi, Confirm there's no extra spaces in both coulums. rgds, rudy "Dave Peterson" wrote: 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 -- Dave Peterson |
COUNTIF
Hi All,
Biff's formula works, Fred was right, needs a bit of motivation. Thanks guys rgds, rudy "Fred Smith" wrote: Rudy, you have two choices. You can choose to be a victim, or you can find the solution. Right now, you are choosing the victim route. You want us to commiserate with you that it's not your fault you can't find a solution. But if you really want a solution, it won't be that hard to find. Check for: * Are your ranges correct? Is your data really in L1:L100 and M1:M100? * Is the underlying data correct? * What happens if you break down the process into steps? What happens if you check for just the Y and the N? What happens if you do a Countif against the data? The solution is there. You just need to be motivated to find it. Regards, Fred. "Rudy" wrote in message ... Hi, Confirm there's no extra spaces in both coulums. rgds, rudy "Dave Peterson" wrote: 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 |
| All times are GMT +1. The time now is 02:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com