| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with SUMPRODUCT
situation...
ColA ColD Norton AV 78 Norton Suite 12 Norton Spy 34 Norton Anti-Virus 56 Search each row in ColA, if contains "Norton", add value from ColD to the SUM. What I have so far, but it is adding ecerything in ColD, even if it does not contain "Norton" In this case, A3 is the value "Norton", and US is the sheet name it is searching. =SUMPRODUCT(--(IF(ISNUMBER(SEARCH(A3,US!A:A)),US!D:D))) thx in advance! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with SUMPRODUCT
=SUMIF(A:A,"*Norton*",B:B)
-- Regards, Peo Sjoblom "Livin" wrote in message ... situation... ColA ColD Norton AV 78 Norton Suite 12 Norton Spy 34 Norton Anti-Virus 56 Search each row in ColA, if contains "Norton", add value from ColD to the SUM. What I have so far, but it is adding ecerything in ColD, even if it does not contain "Norton" In this case, A3 is the value "Norton", and US is the sheet name it is searching. =SUMPRODUCT(--(IF(ISNUMBER(SEARCH(A3,US!A:A)),US!D:D))) thx in advance! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with SUMPRODUCT
COOL!
So I now have... =SUMIF(US!A:A,"*"&A3&"*",US!D:D) how do I convert the US into a value from a cell? this value will change depending on the column and I want to use the column header for the value. thanks! On Aug 18, 2:59*pm, "Peo Sjoblom" wrote: =SUMIF(A:A,"*Norton*",B:B) -- Regards, Peo Sjoblom "Livin" wrote in message ... situation... ColA * * * * * * * * * *ColD Norton AV * * * * * *78 Norton Suite * * * * 12 Norton Spy * * * * * 34 Norton Anti-Virus * 56 Search each row in ColA, if contains "Norton", add value from ColD to the SUM. What I have so far, but it is adding ecerything in ColD, even if it does not contain "Norton" In this case, A3 is the value "Norton", and US is the sheet name it is searching. =SUMPRODUCT(--(IF(ISNUMBER(SEARCH(A3,US!A:A)),US!D:D))) thx in advance!- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with SUMPRODUCT
Maybe...
=SUMIF(INDIRECT("'"&$X$1&"'!A:A"),"*"&A3&"*",INDIR ECT("'"&$X$1&"'!D:D")) Change $x$1 to the address that contains the name of the worksheet. Livin wrote: COOL! So I now have... =SUMIF(US!A:A,"*"&A3&"*",US!D:D) how do I convert the US into a value from a cell? this value will change depending on the column and I want to use the column header for the value. thanks! On Aug 18, 2:59 pm, "Peo Sjoblom" wrote: =SUMIF(A:A,"*Norton*",B:B) -- Regards, Peo Sjoblom "Livin" wrote in message ... situation... ColA ColD Norton AV 78 Norton Suite 12 Norton Spy 34 Norton Anti-Virus 56 Search each row in ColA, if contains "Norton", add value from ColD to the SUM. What I have so far, but it is adding ecerything in ColD, even if it does not contain "Norton" In this case, A3 is the value "Norton", and US is the sheet name it is searching. =SUMPRODUCT(--(IF(ISNUMBER(SEARCH(A3,US!A:A)),US!D:D))) thx in advance!- Hide quoted text - - Show quoted text - -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with SUMPRODUCT
Assume you have the first header in A1
=SUMIF(INDIRECT("'"&A1&"'!A:A"),"*"&A3&"*",US!$D:$ D) Except for A3 it will not change when copied across -- Regards, Peo Sjoblom "Livin" wrote in message ... COOL! So I now have... =SUMIF(US!A:A,"*"&A3&"*",US!D:D) how do I convert the US into a value from a cell? this value will change depending on the column and I want to use the column header for the value. thanks! On Aug 18, 2:59 pm, "Peo Sjoblom" wrote: =SUMIF(A:A,"*Norton*",B:B) -- Regards, Peo Sjoblom "Livin" wrote in message ... situation... ColA ColD Norton AV 78 Norton Suite 12 Norton Spy 34 Norton Anti-Virus 56 Search each row in ColA, if contains "Norton", add value from ColD to the SUM. What I have so far, but it is adding ecerything in ColD, even if it does not contain "Norton" In this case, A3 is the value "Norton", and US is the sheet name it is searching. =SUMPRODUCT(--(IF(ISNUMBER(SEARCH(A3,US!A:A)),US!D:D))) thx in advance!- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with SUMPRODUCT
Doh! It would have helped if I replaced the other sheet reference as well
-- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Assume you have the first header in A1 =SUMIF(INDIRECT("'"&A1&"'!A:A"),"*"&A3&"*",US!$D:$ D) Except for A3 it will not change when copied across -- Regards, Peo Sjoblom "Livin" wrote in message ... COOL! So I now have... =SUMIF(US!A:A,"*"&A3&"*",US!D:D) how do I convert the US into a value from a cell? this value will change depending on the column and I want to use the column header for the value. thanks! On Aug 18, 2:59 pm, "Peo Sjoblom" wrote: =SUMIF(A:A,"*Norton*",B:B) -- Regards, Peo Sjoblom "Livin" wrote in message ... situation... ColA ColD Norton AV 78 Norton Suite 12 Norton Spy 34 Norton Anti-Virus 56 Search each row in ColA, if contains "Norton", add value from ColD to the SUM. What I have so far, but it is adding ecerything in ColD, even if it does not contain "Norton" In this case, A3 is the value "Norton", and US is the sheet name it is searching. =SUMPRODUCT(--(IF(ISNUMBER(SEARCH(A3,US!A:A)),US!D:D))) thx in advance!- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with SUMPRODUCT
I solved the formula with your help!
=SUMIF(INDIRECT("'"&B$2&"'!$A:$A"),"*"&$A3&"*",IND IRECT("'"&B$2&"'!$D: $D")) thank you!!! On Aug 18, 3:49*pm, "Peo Sjoblom" wrote: Doh! It would have helped if I replaced the other sheet reference as well -- Regards, Peo Sjoblom "Peo Sjoblom" wrote in message ... Assume you have the first header in A1 =SUMIF(INDIRECT("'"&A1&"'!A:A"),"*"&A3&"*",US!$D:$ D) Except for A3 it will not change when copied across -- Regards, Peo Sjoblom "Livin" wrote in message .... COOL! So I now have... =SUMIF(US!A:A,"*"&A3&"*",US!D:D) how do I convert the US into a value from a cell? this value will change depending on the column and I want to use the column header for the value. thanks! On Aug 18, 2:59 pm, "Peo Sjoblom" wrote: =SUMIF(A:A,"*Norton*",B:B) -- Regards, Peo Sjoblom "Livin" wrote in message .... situation... ColA ColD Norton AV 78 Norton Suite 12 Norton Spy 34 Norton Anti-Virus 56 Search each row in ColA, if contains "Norton", add value from ColD to the SUM. What I have so far, but it is adding ecerything in ColD, even if it does not contain "Norton" In this case, A3 is the value "Norton", and US is the sheet name it is searching. =SUMPRODUCT(--(IF(ISNUMBER(SEARCH(A3,US!A:A)),US!D:D))) thx in advance!- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with SUMPRODUCT
I tried
=SUMIF(INDIRECT("'"&A1&"'!$A:$A"),"*"&A3&"*",INDIR ECT("'"&A1&"'!$D: $D")) and =SUMIF(INDIRECT("'"&A1&"'!A:A"),"*"&A3&"*",INDIREC T("'"&A1&"'!D:D")) throws #REF with each we are close, thanks for the continuing help! On Aug 18, 3:45*pm, "Peo Sjoblom" wrote: Assume you have the first header in A1 =SUMIF(INDIRECT("'"&A1&"'!A:A"),"*"&A3&"*",US!$D:$ D) Except for A3 it will not change when copied across -- Regards, Peo Sjoblom "Livin" wrote in message ... COOL! So I now have... =SUMIF(US!A:A,"*"&A3&"*",US!D:D) how do I convert the US into a value from a cell? this value will change depending on the column and I want to use the column header for the value. thanks! On Aug 18, 2:59 pm, "Peo Sjoblom" wrote: =SUMIF(A:A,"*Norton*",B:B) -- Regards, Peo Sjoblom "Livin" wrote in message ... situation... ColA ColD Norton AV 78 Norton Suite 12 Norton Spy 34 Norton Anti-Virus 56 Search each row in ColA, if contains "Norton", add value from ColD to the SUM. What I have so far, but it is adding ecerything in ColD, even if it does not contain "Norton" In this case, A3 is the value "Norton", and US is the sheet name it is searching. =SUMPRODUCT(--(IF(ISNUMBER(SEARCH(A3,US!A:A)),US!D:D))) thx in advance!- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with SUMPRODUCT
I also need to make the A:A and D:D static when I copy across cells...
I thought A$:A$ would work? On Aug 18, 2:59*pm, "Peo Sjoblom" wrote: =SUMIF(A:A,"*Norton*",B:B) -- Regards, Peo Sjoblom "Livin" wrote in message ... situation... ColA * * * * * * * * * *ColD Norton AV * * * * * *78 Norton Suite * * * * 12 Norton Spy * * * * * 34 Norton Anti-Virus * 56 Search each row in ColA, if contains "Norton", add value from ColD to the SUM. What I have so far, but it is adding ecerything in ColD, even if it does not contain "Norton" In this case, A3 is the value "Norton", and US is the sheet name it is searching. =SUMPRODUCT(--(IF(ISNUMBER(SEARCH(A3,US!A:A)),US!D:D))) thx in advance!- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with SUMPRODUCT
Try $a:$a
Livin wrote: I also need to make the A:A and D:D static when I copy across cells... I thought A$:A$ would work? On Aug 18, 2:59 pm, "Peo Sjoblom" wrote: =SUMIF(A:A,"*Norton*",B:B) -- Regards, Peo Sjoblom "Livin" wrote in message ... situation... ColA ColD Norton AV 78 Norton Suite 12 Norton Spy 34 Norton Anti-Virus 56 Search each row in ColA, if contains "Norton", add value from ColD to the SUM. What I have so far, but it is adding ecerything in ColD, even if it does not contain "Norton" In this case, A3 is the value "Norton", and US is the sheet name it is searching. =SUMPRODUCT(--(IF(ISNUMBER(SEARCH(A3,US!A:A)),US!D:D))) thx in advance!- Hide quoted text - - Show quoted text - -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| Sumproduct using and, and or | Excel Discussion (Misc queries) | |||
| Sumproduct again? | Excel Worksheet Functions | |||
| SUMPRODUCT except for | New Users to Excel | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Hybrid Mode
