|
how to count one value or another across multiple columns?
I have thre columns: One holds names (record keys), another could hold "X",
the last can have a "Y". I want to know how many records have either an "X" or a "Y" value? For example: Column A Column B Column C Matt X Bill Y John X Y Fred X Y Tom How many records (i.e. column A) have either an X or Y? |
Hi!
What result do you expect based on your example? 6 or 4 ? Biff "MeatLightning" wrote in message ... I have thre columns: One holds names (record keys), another could hold "X", the last can have a "Y". I want to know how many records have either an "X" or a "Y" value? For example: Column A Column B Column C Matt X Bill Y John X Y Fred X Y Tom How many records (i.e. column A) have either an X or Y? |
Doh!
It would help if I read the entire post! How many records (i.e. column A) have either an X or Y? Try this: =COUNTA(A2:A6)-SUMPRODUCT(--(ISBLANK(B2:B6)),--(ISBLANK(C2:C6))) Will work as long as there are no empty rows within the range. Biff "Biff" wrote in message ... Hi! What result do you expect based on your example? 6 or 4 ? Biff "MeatLightning" wrote in message ... I have thre columns: One holds names (record keys), another could hold "X", the last can have a "Y". I want to know how many records have either an "X" or a "Y" value? For example: Column A Column B Column C Matt X Bill Y John X Y Fred X Y Tom How many records (i.e. column A) have either an X or Y? |
|
|
Hmmm.. well I did have one space in there... but removing it didn't appear to
help. I know get "6" for the OR equation. The AND equation result stayed the same ("2") The answer I'm looking for in the OR equation is "4"... because four records (i.e. rows or specifically the items in column A) contain either an X or a Y. "Franz" wrote: "MeatLightning" ha scritto nel messaggio Thanks for the help!!! I don't think this works though?... Using the =SUMPRODUCT((B1:B5="X")*(C1:C5="Y")) formula you have for finding which records have X *AND* Y, I get "1"... using my example data, it should be "2" Using the =SUMPRODUCT((B1:B5="X")+(C1:C5="Y")) formual you have for finding which records have X *OR* Y, I get "5"... is should be "4" IMHO with your data should be "6"... Any ideas? Maybe you have also spaces or blanks in the cell with X or Y; in this case you can try these: =SUMPRODUCT((RIGHT(B1:B5,1)="X")+(RIGHT(C1:C5)="Y" )) =SUMPRODUCT((RIGHT(B1:B5,1)="X")*(RIGHT(C1:C5,1)=" Y")) -- Hoping to be helpful... Regards Franz ---------------------------------------------------------------------------------------- To reply translate from italian InVento (no capital letters) ---------------------------------------------------------------------------------------- |
|
I think that's done it!! THANKS A BUNCH!!!
"Franz" wrote: "MeatLightning" ha scritto nel messaggio Hmmm.. well I did have one space in there... but removing it didn't appear to help. I think you should also have a leading blank before each X or Y (I think so because I found it in your sample data...). I know get "6" for the OR equation. The AND equation result stayed the same ("2") The answer I'm looking for in the OR equation is "4"... because four records (i.e. rows or specifically the items in column A) contain either an X or a Y. Try this one, array entered (i.e. ctrl+shift+enter, instead of enter): =SUMPRODUCT(IF((B1:B5="X")+(C1:C5="Y"),1,0)) -- Hoping to be helpful... Regards Franz ---------------------------------------------------------------------------------------- To reply translate from italian InVento (no capital letters) ---------------------------------------------------------------------------------------- |
"MeatLightning" ha scritto nel
messaggio I think that's done it!! THANKS A BUNCH!!! Your welcome. Thanks for the feedback -- Glad to be helpful... Regards Franz ---------------------------------------------------------------------------------------- To reply translate from italian InVento (no capital letters) ---------------------------------------------------------------------------------------- |
=SUMPRODUCT(--(($B$2:$B$6="X")+($C$2:$C$6="Y")0))
MeatLightning wrote: I have thre columns: One holds names (record keys), another could hold "X", the last can have a "Y". I want to know how many records have either an "X" or a "Y" value? For example: Column A Column B Column C Matt X Bill Y John X Y Fred X Y Tom How many records (i.e. column A) have either an X or Y? |
| All times are GMT +1. The time now is 02:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com