| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
I hope someone can help me with a formula
I have a spreadsheet set up and need a formula to count the number of entries
in a row IF another row's criteria matches. ie: COUNT(IF(B3:IV3,"Excellent"),IF(B1:IV1,"A07C,A06C, A18C) The problem I'm encountering is that the 'count' formula does't seem to work with multiple criteria...does anyone have any suggestions? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I hope someone can help me with a formula
=Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A07C"))+Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A06C"))+Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A18C"))
would be one guess at what you want. -- Regards, Tom Ogilvy "caddly" wrote: I have a spreadsheet set up and need a formula to count the number of entries in a row IF another row's criteria matches. ie: COUNT(IF(B3:IV3,"Excellent"),IF(B1:IV1,"A07C,A06C, A18C) The problem I'm encountering is that the 'count' formula does't seem to work with multiple criteria...does anyone have any suggestions? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I hope someone can help me with a formula
Hi there...I tried your suggestion, but it isn't working either? Any other
suggestions? I know it's difficult when you don't have the sheet in front of you, but any suggestions would be very helpful. This is a statistical report I'm trying to formulate with much criteria involved, as you can see. =SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A07C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A06C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A09C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A27C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A28C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A18C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A12C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A10C")) "Tom Ogilvy" wrote: =Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A07C"))+Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A06C"))+Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A18C")) would be one guess at what you want. -- Regards, Tom Ogilvy "caddly" wrote: I have a spreadsheet set up and need a formula to count the number of entries in a row IF another row's criteria matches. ie: COUNT(IF(B3:IV3,"Excellent"),IF(B1:IV1,"A07C,A06C, A18C) The problem I'm encountering is that the 'count' formula does't seem to work with multiple criteria...does anyone have any suggestions? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I hope someone can help me with a formula
Just saying it doesn't work doesn't help much since it works for what I wrote
it for/how I interpreted what you wanted. Your formula should give you the count of columns from B to IV that have Excellent in row 3 and any of those 4 digit codes in row 1. If that description matches what you want, and you are not getting that from the formula, time to look at the data and make sure it is clean. -- Regards, Tom Ogilvy "caddly" wrote: Hi there...I tried your suggestion, but it isn't working either? Any other suggestions? I know it's difficult when you don't have the sheet in front of you, but any suggestions would be very helpful. This is a statistical report I'm trying to formulate with much criteria involved, as you can see. =SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A07C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A06C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A09C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A27C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A28C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A18C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A12C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A10C")) "Tom Ogilvy" wrote: =Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A07C"))+Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A06C"))+Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A18C")) would be one guess at what you want. -- Regards, Tom Ogilvy "caddly" wrote: I have a spreadsheet set up and need a formula to count the number of entries in a row IF another row's criteria matches. ie: COUNT(IF(B3:IV3,"Excellent"),IF(B1:IV1,"A07C,A06C, A18C) The problem I'm encountering is that the 'count' formula does't seem to work with multiple criteria...does anyone have any suggestions? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I hope someone can help me with a formula
Thanks so much for your help thus far. Can you tell me what you mean by
making sure my data is clean? (I'm a novice at this...trying to learn) "Tom Ogilvy" wrote: Just saying it doesn't work doesn't help much since it works for what I wrote it for/how I interpreted what you wanted. Your formula should give you the count of columns from B to IV that have Excellent in row 3 and any of those 4 digit codes in row 1. If that description matches what you want, and you are not getting that from the formula, time to look at the data and make sure it is clean. -- Regards, Tom Ogilvy "caddly" wrote: Hi there...I tried your suggestion, but it isn't working either? Any other suggestions? I know it's difficult when you don't have the sheet in front of you, but any suggestions would be very helpful. This is a statistical report I'm trying to formulate with much criteria involved, as you can see. =SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A07C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A06C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A09C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A27C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A28C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A18C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A12C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A10C")) "Tom Ogilvy" wrote: =Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A07C"))+Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A06C"))+Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A18C")) would be one guess at what you want. -- Regards, Tom Ogilvy "caddly" wrote: I have a spreadsheet set up and need a formula to count the number of entries in a row IF another row's criteria matches. ie: COUNT(IF(B3:IV3,"Excellent"),IF(B1:IV1,"A07C,A06C, A18C) The problem I'm encountering is that the 'count' formula does't seem to work with multiple criteria...does anyone have any suggestions? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
I hope someone can help me with a formula
Oh my gosh...I figured it out, thanks to you...I was omitting the 'sheet1!'
reference and wasn't getting a count, but I inserted that in front and have the correct count now! Thank you so much for your patience! "Tom Ogilvy" wrote: If there are blanks or something in your cells so that Excellent is really "Excellent " as an example. Send me a sample workbook with an explanation of what you expect the results to be and why and I will see what I can come up with -- Regards, Tom Ogilvy "caddly" wrote: Thanks so much for your help thus far. Can you tell me what you mean by making sure my data is clean? (I'm a novice at this...trying to learn) "Tom Ogilvy" wrote: Just saying it doesn't work doesn't help much since it works for what I wrote it for/how I interpreted what you wanted. Your formula should give you the count of columns from B to IV that have Excellent in row 3 and any of those 4 digit codes in row 1. If that description matches what you want, and you are not getting that from the formula, time to look at the data and make sure it is clean. -- Regards, Tom Ogilvy "caddly" wrote: Hi there...I tried your suggestion, but it isn't working either? Any other suggestions? I know it's difficult when you don't have the sheet in front of you, but any suggestions would be very helpful. This is a statistical report I'm trying to formulate with much criteria involved, as you can see. =SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A07C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A06C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A09C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A27C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A28C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A18C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A12C"))+SUMPRODUCT(--(B3:IV3="Excellent"),--(B1:IV1="A10C")) "Tom Ogilvy" wrote: =Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A07C"))+Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A06C"))+Sumproduct(--(B3:IV3="Excellent),--(B1:IV1="A18C")) would be one guess at what you want. -- Regards, Tom Ogilvy "caddly" wrote: I have a spreadsheet set up and need a formula to count the number of entries in a row IF another row's criteria matches. ie: COUNT(IF(B3:IV3,"Excellent"),IF(B1:IV1,"A07C,A06C, A18C) The problem I'm encountering is that the 'count' formula does't seem to work with multiple criteria...does anyone have any suggestions? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simple Formula Questions (I hope) | New Users to Excel | |||
| Hope someone can help me | Excel Discussion (Misc queries) | |||
| Hope Someone Can Help... | Excel Programming | |||
| Last Hope -HELP!! | Excel Programming | |||
| Last Hope -HELP!! | Excel Programming | |||
Linear Mode
