If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count functions
Hi,
I would like to count the number of records which have a certain value in one column and a certain value in a different column. E.g. Each record in column B is marked A-C Each record in column C is marked Y or N I would like to count how many records are marked A and Y; A and N; B and Y; B and N; C and Y; C and N Is this possible? |
#2
|
|||
|
|||
Count functions
Have a look here...
http://www.xl-central.com/count-multiple-criteria.html Hope this helps! http://www.xl-central.com In article , Jalal wrote: Hi, I would like to count the number of records which have a certain value in one column and a certain value in a different column. E.g. Each record in column B is marked A-C Each record in column C is marked Y or N I would like to count how many records are marked A and Y; A and N; B and Y; B and N; C and Y; C and N Is this possible? |
#3
|
|||
|
|||
Count functions
Hi Domenic,
Many thanks for your speedy response. I have used the SUMPRODUCT function you suggested... =SUMPRODUCT(--(D2:$D$65536=T2),--(E2:$E$65536=U2)) and it works!... Now... what about if I have 3 variables... is it possible to count the number of records that conform to the following: A-Y-JJ B-Y-JJ C-Y-JJ A-N-JJ B-N-JJ C-N-JJ A-Y-AC B-Y-AC C-Y-AC A-N-AC B-N-AC C-N-AC A-Y-NB B-Y-NB C-Y-NB A-N-NB B-N-NB C-N-NB etc. etc.? I have tried to add it to the SUMPRODUCT formula you initially suggested but get an impossibly HIGH answer - it cannot be correct e.g. this is the formula i created =SUMPRODUCT(--(D2:$D$65536=T2),--(E2:$E$65536=U2)--(C2:$C$65536=V2)) Any suggestions? "Domenic" wrote: Have a look here... http://www.xl-central.com/count-multiple-criteria.html Hope this helps! http://www.xl-central.com In article , Jalal wrote: Hi, I would like to count the number of records which have a certain value in one column and a certain value in a different column. E.g. Each record in column B is marked A-C Each record in column C is marked Y or N I would like to count how many records are marked A and Y; A and N; B and Y; B and N; C and Y; C and N Is this possible? |
#4
|
|||
|
|||
Count functions
In article ,
Jalal wrote: I have tried to add it to the SUMPRODUCT formula you initially suggested but get an impossibly HIGH answer - it cannot be correct e.g. this is the formula i created =SUMPRODUCT(--(D2:$D$65536=T2),--(E2:$E$65536=U2)--(C2:$C$65536=V2)) Any suggestions? There's a comma missing between the second and third argument. Try... =SUMPRODUCT(--(D2:$D$65536=T2),--(E2:$E$65536=U2),--(C2:$C$65536=V2)) Also, if your data does not actually extent all the way to Row 65536 and you're using Excel 2003, convert your data into a list... Data List Create List The ranges will automatically adjust as data is added/removed. If you're using an earlier version, you can use dynamic named ranges. -- Domenic http://www.xl-central.com |
#5
|
|||
|
|||
Count functions
Perfect
Both tips work a treat - many thanks! "Domenic" wrote: In article , Jalal wrote: I have tried to add it to the SUMPRODUCT formula you initially suggested but get an impossibly HIGH answer - it cannot be correct e.g. this is the formula i created =SUMPRODUCT(--(D2:$D$65536=T2),--(E2:$E$65536=U2)--(C2:$C$65536=V2)) Any suggestions? There's a comma missing between the second and third argument. Try... =SUMPRODUCT(--(D2:$D$65536=T2),--(E2:$E$65536=U2),--(C2:$C$65536=V2)) Also, if your data does not actually extent all the way to Row 65536 and you're using Excel 2003, convert your data into a list... Data List Create List The ranges will automatically adjust as data is added/removed. If you're using an earlier version, you can use dynamic named ranges. -- Domenic http://www.xl-central.com |
Thread Tools | |
Display Modes | |
|
|