Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the number when you have a number and text
I have a column for EE or SP and a column that has an amount of insurance
coverage like 10,000 etc. I want to count the total number of EE have that level or type of coverage My columns are like this: C1 EE K1 10,000 CE SP K2 200,00 I have tried numerous formulas from the various discussions and I can't find any that works. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the number when you have a number and text
I think I understand your question and you will just want to do a
==COUNTIF(A1:A4,"EE") A1:A4 needs to be adjusted for your data range. HTH |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the number when you have a number and text
not sure where that double ='s came from...but you only need one.
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the number when you have a number and text
Assuming col B houses: EE, SP, etc and col D contains the figures
we could do it in say, F1: =SUMPRODUCT(($B$1:$B$10="EE")*($D$1:$D$10=10000)) Adapt the ranges to suit, but note that we can't use entire col references (eg: A:A, B:B) in SUMPRODUCT -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "HRLADY" wrote: I have a column for EE or SP and a column that has an amount of insurance coverage like 10,000 etc. I want to count the total number of EE have that level or type of coverage My columns are like this: C1 EE K1 10,000 CE SP K2 200,00 I have tried numerous formulas from the various discussions and I can't find any that works. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the number when you have a number and text
Hi, did you try COUNTIF?
Assuming that the 10,000 are on the column D, "=contif(d2:d1000,"10000") hope it helps Marcelo - Brazil "HRLADY" escreveu: I have a column for EE or SP and a column that has an amount of insurance coverage like 10,000 etc. I want to count the total number of EE have that level or type of coverage My columns are like this: C1 EE K1 10,000 CE SP K2 200,00 I have tried numerous formulas from the various discussions and I can't find any that works. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the number when you have a number and text
It didn't work it gave me 0. Also note that the figure with numbers verify. I
want the number of EE's that have any amount in the other column. "Max" wrote: Assuming col B houses: EE, SP, etc and col D contains the figures we could do it in say, F1: =SUMPRODUCT(($B$1:$B$10="EE")*($D$1:$D$10=10000)) Adapt the ranges to suit, but note that we can't use entire col references (eg: A:A, B:B) in SUMPRODUCT -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "HRLADY" wrote: I have a column for EE or SP and a column that has an amount of insurance coverage like 10,000 etc. I want to count the total number of EE have that level or type of coverage My columns are like this: C1 EE K1 10,000 CE SP K2 200,00 I have tried numerous formulas from the various discussions and I can't find any that works. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the number when you have a number and text
No, I want to count the number of EE that also have an amount of insurance
"ChuckF" wrote: I think I understand your question and you will just want to do a ==COUNTIF(A1:A4,"EE") A1:A4 needs to be adjusted for your data range. HTH |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the number when you have a number and text
maybe the pivot table is the best solution to you
Marcelo - Brazil "HRLADY" escreveu: It didn't work it gave me 0. Also note that the figure with numbers verify. I want the number of EE's that have any amount in the other column. "Max" wrote: Assuming col B houses: EE, SP, etc and col D contains the figures we could do it in say, F1: =SUMPRODUCT(($B$1:$B$10="EE")*($D$1:$D$10=10000)) Adapt the ranges to suit, but note that we can't use entire col references (eg: A:A, B:B) in SUMPRODUCT -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "HRLADY" wrote: I have a column for EE or SP and a column that has an amount of insurance coverage like 10,000 etc. I want to count the total number of EE have that level or type of coverage My columns are like this: C1 EE K1 10,000 CE SP K2 200,00 I have tried numerous formulas from the various discussions and I can't find any that works. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the number when you have a number and text
"HRLADY" wrote:
It didn't work it gave me 0. Also note that the figure with numbers verify. I want the number of EE's that have any amount in the other column. Try instead in say, F1 something like: =SUMPRODUCT(($B$1:$B$10="EE")*($D$1:$D$10<"")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik ---. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the number when you have a number and text
A B C D
1 C1 EE K1 10,000 2 CE SP K2 200,000 3 C1 EE K1 10,000 4 CE EE K2 =SUMPRODUCT((B1:B4="EE")*(D1:D40)) You get an answer of 2. There are two enterys where EE is in colulmn B and there is a dollar value colulmn D. You will obviously need to change B4 and D4 to whatever it is that you need it to be. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the number when you have a number and text
Max, its worked here.
Marcelo - Brazil "Max" escreveu: "HRLADY" wrote: It didn't work it gave me 0. Also note that the figure with numbers verify. I want the number of EE's that have any amount in the other column. Try instead in say, F1 something like: =SUMPRODUCT(($B$1:$B$10="EE")*($D$1:$D$10<"")) -- Max Singapore http://savefile.com/projects/236895 xdemechanik ---. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting the number when you have a number and text
"Marcelo" wrote:
Max, its worked here. Marcelo - Brazil Glad to hear that, Marcelo ! Thanks for feedback Hope it works for HRLady, too .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting the number of decimal places for a text box. | Excel Discussion (Misc queries) | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Change number format from text to number? | New Users to Excel | |||
How do I look up a number within a string of text | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions |