Count NonBlank Rows with Criteria in another column
I need to count the number of nonblank rows that have a certain
criteria in a different column. I assume that some version of the SUMPRODUCT formula will do what I need to do, but I can't figure it out for the life of me. Any ideas? Say I have an array of data entered into cells K3:N35. I need to count the number of rows that are nonblank in any of the cells in that array that also have an "A" in column O. I think it should be something like: =SUMPRODUCT(--('Data Sheet'!K3:N35<""),--('Data Sheet'!O3:O35="A")) Your assistance is most appreciated. Carmen |
Count NonBlank Rows with Criteria in another column
What error do you get?
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com wrote in message ... I need to count the number of nonblank rows that have a certain criteria in a different column. I assume that some version of the SUMPRODUCT formula will do what I need to do, but I can't figure it out for the life of me. Any ideas? Say I have an array of data entered into cells K3:N35. I need to count the number of rows that are nonblank in any of the cells in that array that also have an "A" in column O. I think it should be something like: =SUMPRODUCT(--('Data Sheet'!K3:N35<""),--('Data Sheet'!O3:O35="A")) Your assistance is most appreciated. Carmen |
Count NonBlank Rows with Criteria in another column
|
Count NonBlank Rows with Criteria in another column
I'm assuming you want to count a row if there is at least one entry in that
row. =SUMPRODUCT(--(MMULT(--(K3:N35<""),{1;1;1;1})0),--(O3:O35="A")) -- Biff Microsoft Excel MVP wrote in message ... I need to count the number of nonblank rows that have a certain criteria in a different column. I assume that some version of the SUMPRODUCT formula will do what I need to do, but I can't figure it out for the life of me. Any ideas? Say I have an array of data entered into cells K3:N35. I need to count the number of rows that are nonblank in any of the cells in that array that also have an "A" in column O. I think it should be something like: =SUMPRODUCT(--('Data Sheet'!K3:N35<""),--('Data Sheet'!O3:O35="A")) Your assistance is most appreciated. Carmen |
Count NonBlank Rows with Criteria in another column
On May 5, 9:28*pm, "T. Valko" wrote:
I'm assuming you want to count a row if there is at least one entry in that row. =SUMPRODUCT(--(MMULT(--(K3:N35<""),{1;1;1;1})0),--(O3:O35="A")) -- Biff Microsoft Excel MVP wrote in message ... I need to count the number of nonblank rows that have a certain criteria in a different column. *I assume that some version of the SUMPRODUCT formula will do what I need to do, but I can't figure it out for the life of me. Any ideas? Say I have an array of data entered into cells K3:N35. *I need to count the number of rows that are nonblank in any of the cells in that array that also have an "A" in column O. *I think it should be something like: =SUMPRODUCT(--('Data Sheet'!K3:N35<""),--('Data Sheet'!O3:O35="A")) Your assistance is most appreciated. Carmen- Hide quoted text - - Show quoted text - Biff, This does it! Thanks so much. You totally saved my project. 8-D Carmen |
Count NonBlank Rows with Criteria in another column
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP wrote in message ... On May 5, 9:28 pm, "T. Valko" wrote: I'm assuming you want to count a row if there is at least one entry in that row. =SUMPRODUCT(--(MMULT(--(K3:N35<""),{1;1;1;1})0),--(O3:O35="A")) -- Biff Microsoft Excel MVP wrote in message ... I need to count the number of nonblank rows that have a certain criteria in a different column. I assume that some version of the SUMPRODUCT formula will do what I need to do, but I can't figure it out for the life of me. Any ideas? Say I have an array of data entered into cells K3:N35. I need to count the number of rows that are nonblank in any of the cells in that array that also have an "A" in column O. I think it should be something like: =SUMPRODUCT(--('Data Sheet'!K3:N35<""),--('Data Sheet'!O3:O35="A")) Your assistance is most appreciated. Carmen- Hide quoted text - - Show quoted text - Biff, This does it! Thanks so much. You totally saved my project. 8-D Carmen |
Count NonBlank Rows with Criteria in another column
T. Valko wrote:
I'm assuming you want to count a row if there is at least one entry in that row. =SUMPRODUCT(--(MMULT(--(K3:N35<""),{1;1;1;1})0),--(O3:O35="A")) Nice solution Biff! And thanks for writing about MMULT... this was unknown to me, and I suspect many others as well. |
Count NonBlank Rows with Criteria in another column
Thanks!
-- Biff Microsoft Excel MVP "smartin" wrote in message ... T. Valko wrote: I'm assuming you want to count a row if there is at least one entry in that row. =SUMPRODUCT(--(MMULT(--(K3:N35<""),{1;1;1;1})0),--(O3:O35="A")) Nice solution Biff! And thanks for writing about MMULT... this was unknown to me, and I suspect many others as well. |
All times are GMT +1. The time now is 06:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com