Need help with SUMPRODUCT

[Livin]

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)))


thx in advance!

 

[Peo Sjoblom]

=SUMIF(A:A,"*Norton*",B:B)

--


Regards,


Peo Sjoblom

 

[Livin]

COOL!

So I now have...

=SUMIF(US!A:A,"*"&A3&"*",US!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!

 

[Livin]

I also need to make the A:A and D static when I copy across cells...
I thought A$:A$ would work?

 

[Dave Peterson]

Maybe...

=SUMIF(INDIRECT("'"&$X$1&"'!A:A"),"*"&A3&"*",INDIRECT("'"&$X$1&"'!D"))

Change $x$1 to the address that contains the name of the worksheet.

 

[Peo Sjoblom]

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

COOL!

So I now have...

=SUMIF(US!A:A,"*"&A3&"*",US!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!

 

[Peo Sjoblom]

Doh! It would have helped if I replaced the other sheet reference as well

--


Regards,


Peo Sjoblom

 

[Dave Peterson]

Try $a:$a

I also need to make the A:A and D static when I copy across cells...
I thought A$:A$ would work?

 

[Livin]

I tried

=SUMIF(INDIRECT("'"&A1&"'!$A:$A"),"*"&A3&"*",INDIRECT("'"&A1&"'!$D:
$D"))

and

=SUMIF(INDIRECT("'"&A1&"'!A:A"),"*"&A3&"*",INDIRECT("'"&A1&"'!D"))

throws #REF with each

we are close, thanks for the continuing help!

 

[Livin]

I solved the formula with your help!

=SUMIF(INDIRECT("'"&B$2&"'!$A:$A"),"*"&$A3&"*",INDIRECT("'"&B$2&"'!$D:
$D"))

thank you!!!