Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct - number & text
Hi, I have number in colB & text in Col C,D
I was trying sumproduct to go the data from colD looking at Col B & C something like below.. But result is #VALUE! =SUMPRODUCT((Sheet2!$C$2:$C$100=$C$2)*(Sheet2!$B$2 :$B$100=R4)*(Sheet2!$D$2:$D$100)) Can someone help me on this pls... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct - number & text
How can you 'SUM' text values?
"Kashyap" wrote: Hi, I have number in colB & text in Col C,D I was trying sumproduct to go the data from colD looking at Col B & C something like below.. But result is #VALUE! =SUMPRODUCT((Sheet2!$C$2:$C$100=$C$2)*(Sheet2!$B$2 :$B$100=R4)*(Sheet2!$D$2:$D$100)) Can someone help me on this pls... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct - number & text
If you use that * operation, then if you have any non-numeric entries in
D2:D100, then you'll get that error. But if you change your syntax: =SUMPRODUCT(--(Sheet2!$C$2:$C$100=$C$2), --(Sheet2!$B$2:$B$100=R4), --(Sheet2!$D$2:$D$100)) Text in D2:D100 will be treated as 0--just like =sum(). Kashyap wrote: Hi, I have number in colB & text in Col C,D I was trying sumproduct to go the data from colD looking at Col B & C something like below.. But result is #VALUE! =SUMPRODUCT((Sheet2!$C$2:$C$100=$C$2)*(Sheet2!$B$2 :$B$100=R4)*(Sheet2!$D$2:$D$100)) Can someone help me on this pls... -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct - number & text
Then what way can I get the corresponding text?
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct - number & text
You'll have to specify how you want to sum the text. Give an example and
show the results you want. Regards, Fred. "Kashyap" wrote in message ... Then what way can I get the corresponding text? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct - number & text
I just want to lookup 2 columns and get the result from the 3rd column. But
here 2 columns are text and number in one column. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct - number & text
See http://www.contextures.com/xlFunctio...ml#IndexMatch4 for a solution
"Kashyap" wrote: I just want to lookup 2 columns and get the result from the 3rd column. But here 2 columns are text and number in one column. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct - number & text
Hi Kayshap
Try this: With the criteria you want to look up in E1 and F1 =INDEX($D$2:$D$100,MATCH($E$1&$F$1,$B$2:$B$100&$C$ 2:$C$100,0)) this is an array formula entered by pressing Ctrl. Shift and Enter together. This should return the value from the row in column D where the cells in columns B and C have the values specified in E1 and F1. "Kashyap" wrote: Hi, I have number in colB & text in Col C,D I was trying sumproduct to go the data from colD looking at Col B & C something like below.. But result is #VALUE! =SUMPRODUCT((Sheet2!$C$2:$C$100=$C$2)*(Sheet2!$B$2 :$B$100=R4)*(Sheet2!$D$2:$D$100)) Can someone help me on this pls... |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct - number & text
Try this array formula** :
=INDEX(Sheet2!$D$2:$D$100,MATCH(1,(Sheet2!$C$2:$C$ 100=$C$2)*(Sheet2!$B$2:$B$100=R4),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Kashyap" wrote in message ... Hi, I have number in colB & text in Col C,D I was trying sumproduct to go the data from colD looking at Col B & C something like below.. But result is #VALUE! =SUMPRODUCT((Sheet2!$C$2:$C$100=$C$2)*(Sheet2!$B$2 :$B$100=R4)*(Sheet2!$D$2:$D$100)) Can someone help me on this pls... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 criteria lookup of text. Return text form column 3. SUMPRODUCT t | Excel Worksheet Functions | |||
Sumproduct with both number and text formats | Excel Worksheet Functions | |||
Sumproduct with either text of number format | Excel Worksheet Functions | |||
Why does this fail? =TEXT(RC3,Number)&" / "&TEXT(R32C,Number) | Excel Worksheet Functions | |||
Sumproduct with different number formats | Excel Discussion (Misc queries) |