| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with cells contents sum
Here is a sample of my column cells contents:
2DO, 5DI (this data is in one cell) 1DO, 2DI AI AO I need to get a total of "DO", total of "DI" ...etc. separately I have tried countif, sumproduct...but not getting the right result! Would appreciate any help. Yasmina |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with cells contents sum
Try this.
=SUMPRODUCT(--(sheet!$D$2:$D$1191="DO"), --(sheet!$V$2:$V$1191="DI Leader")) Change the word SHEET to the tab/sheet name you are referencing from. Hope this helps. -- Miss Kitty "Nanou" wrote: Here is a sample of my column cells contents: 2DO, 5DI (this data is in one cell) 1DO, 2DI AI AO I need to get a total of "DO", total of "DI" ...etc. separately I have tried countif, sumproduct...but not getting the right result! Would appreciate any help. Yasmina |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with cells contents sum
Miss Kitty,
It did not work. returning value of "0". Thanks though! "Miss Kitty" wrote: Try this. =SUMPRODUCT(--(sheet!$D$2:$D$1191="DO"), --(sheet!$V$2:$V$1191="DI Leader")) Change the word SHEET to the tab/sheet name you are referencing from. Hope this helps. -- Miss Kitty "Nanou" wrote: Here is a sample of my column cells contents: 2DO, 5DI (this data is in one cell) 1DO, 2DI AI AO I need to get a total of "DO", total of "DI" ...etc. separately I have tried countif, sumproduct...but not getting the right result! Would appreciate any help. Yasmina |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with cells contents sum
Hi
Try =SUMPRODUCT(--(ISNUMBER(FIND("DO",A1:A100)))) Change range to suit. -- Regards Roger Govier "Nanou" wrote in message ... Here is a sample of my column cells contents: 2DO, 5DI (this data is in one cell) 1DO, 2DI AI AO I need to get a total of "DO", total of "DI" ...etc. separately I have tried countif, sumproduct...but not getting the right result! Would appreciate any help. Yasmina |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with cells contents sum
Hi
Try =SUMPRODUCT(--(ISNUMBER(FIND("DO",A1:A100)))) Change range to suit It might be better to put the "DO" or "DI" in a cell e.g. in C1 , then you can just use the formula =SUMPRODUCT(--(ISNUMBER(FIND(C1,A1:A100)))) -- Regards Roger Govier "Nanou" wrote in message ... Here is a sample of my column cells contents: 2DO, 5DI (this data is in one cell) 1DO, 2DI AI AO I need to get a total of "DO", total of "DI" ...etc. separately I have tried countif, sumproduct...but not getting the right result! Would appreciate any help. Yasmina |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with cells contents sum
Hi Roger,
Thanks for your help. But as I said I already used the formula and what happened is that it is bringing me a wronge count. I explain: Based on the example I posted in my original message it will give me " 2 " as result for my " DO " count , where it should be " 3" (number of actual "DO" ) Thanks for any help! the following result for "do" my example "Roger Govier" wrote: Hi Try =SUMPRODUCT(--(ISNUMBER(FIND("DO",A1:A100)))) Change range to suit It might be better to put the "DO" or "DI" in a cell e.g. in C1 , then you can just use the formula =SUMPRODUCT(--(ISNUMBER(FIND(C1,A1:A100)))) -- Regards Roger Govier "Nanou" wrote in message ... Here is a sample of my column cells contents: 2DO, 5DI (this data is in one cell) 1DO, 2DI AI AO I need to get a total of "DO", total of "DI" ...etc. separately I have tried countif, sumproduct...but not getting the right result! Would appreciate any help. Yasmina |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with cells contents sum
=SUM(LEFT(A1),LEFT(A2))*1
Returns 3 Gord Dibben MS Excel MVP On Tue, 26 Aug 2008 09:35:02 -0700, Nanou wrote: Hi Roger, Thanks for your help. But as I said I already used the formula and what happened is that it is bringing me a wronge count. I explain: Based on the example I posted in my original message it will give me " 2 " as result for my " DO " count , where it should be " 3" (number of actual "DO" ) Thanks for any help! the following result for "do" my example "Roger Govier" wrote: Hi Try =SUMPRODUCT(--(ISNUMBER(FIND("DO",A1:A100)))) Change range to suit It might be better to put the "DO" or "DI" in a cell e.g. in C1 , then you can just use the formula =SUMPRODUCT(--(ISNUMBER(FIND(C1,A1:A100)))) -- Regards Roger Govier "Nanou" wrote in message ... Here is a sample of my column cells contents: 2DO, 5DI (this data is in one cell) 1DO, 2DI AI AO I need to get a total of "DO", total of "DI" ...etc. separately I have tried countif, sumproduct...but not getting the right result! Would appreciate any help. Yasmina |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with cells contents sum
Gord,
what is that * 1 for ? "Gord Dibben" wrote: =SUM(LEFT(A1),LEFT(A2))*1 Returns 3 Gord Dibben MS Excel MVP On Tue, 26 Aug 2008 09:35:02 -0700, Nanou wrote: Hi Roger, Thanks for your help. But as I said I already used the formula and what happened is that it is bringing me a wronge count. I explain: Based on the example I posted in my original message it will give me " 2 " as result for my " DO " count , where it should be " 3" (number of actual "DO" ) Thanks for any help! the following result for "do" my example "Roger Govier" wrote: Hi Try =SUMPRODUCT(--(ISNUMBER(FIND("DO",A1:A100)))) Change range to suit It might be better to put the "DO" or "DI" in a cell e.g. in C1 , then you can just use the formula =SUMPRODUCT(--(ISNUMBER(FIND(C1,A1:A100)))) -- Regards Roger Govier "Nanou" wrote in message ... Here is a sample of my column cells contents: 2DO, 5DI (this data is in one cell) 1DO, 2DI AI AO I need to get a total of "DO", total of "DI" ...etc. separately I have tried countif, sumproduct...but not getting the right result! Would appreciate any help. Yasmina |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with cells contents sum
Just in case the formula returns text.
The *1 changes it back to a usable numeric. Not necessary in this case.............just me out of habit<g Gord On Tue, 26 Aug 2008 11:54:06 -0700, Nanou wrote: Gord, what is that * 1 for ? "Gord Dibben" wrote: =SUM(LEFT(A1),LEFT(A2))*1 Returns 3 Gord Dibben MS Excel MVP On Tue, 26 Aug 2008 09:35:02 -0700, Nanou wrote: Hi Roger, Thanks for your help. But as I said I already used the formula and what happened is that it is bringing me a wronge count. I explain: Based on the example I posted in my original message it will give me " 2 " as result for my " DO " count , where it should be " 3" (number of actual "DO" ) Thanks for any help! the following result for "do" my example "Roger Govier" wrote: Hi Try =SUMPRODUCT(--(ISNUMBER(FIND("DO",A1:A100)))) Change range to suit It might be better to put the "DO" or "DI" in a cell e.g. in C1 , then you can just use the formula =SUMPRODUCT(--(ISNUMBER(FIND(C1,A1:A100)))) -- Regards Roger Govier "Nanou" wrote in message ... Here is a sample of my column cells contents: 2DO, 5DI (this data is in one cell) 1DO, 2DI AI AO I need to get a total of "DO", total of "DI" ...etc. separately I have tried countif, sumproduct...but not getting the right result! Would appreciate any help. Yasmina |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to Split the contents of cells across multiple cells | New Users to Excel | |||
| compare the contents of one range of cells with the contents of a. | Excel Discussion (Misc queries) | |||
| Contents in cells | Excel Discussion (Misc queries) | |||
| Complicated counting of cells (based on other cells contents) | Excel Worksheet Functions | |||
| how do populate empty cells with the contents of populated cells . | Excel Discussion (Misc queries) | |||
Hybrid Mode
