Question on SUMIF vs SUMPRODUCT and how to return a total in one column based on values in other columns

[TMCINNIS]

I have a massive excel spreadsheet of data that contains thousands of URLS and web performance data.

My table structure is:
Col B = Microsite
Col I = URLs
Col S = Total Page Views

I am trying get extract the total in range S3:S1048576 where the values in range B3:B1048576 = "xyz", and the values in range I3:I1048576 = "abc" and that value in the second range is based on a work middle of a string (in this case the URL).

This is the formula that I have now (the one that I had the highest hope for).

=SUMIFS($S$3:$S$1048576,$b$3:$b$1048576="MicrositeA",i3:i1048576=(ISNUMBER(SEARCH("Rebates")))

I also have this formula that returned the count of how many instances it found the word, but did not return the sum of the column with the monthly totals:

'=SUMPRODUCT(($b$3:$b$1048576="MicrositeA")*(i3:i1048576=ISNUMBER(SEARCH("Rebates",$i$3:$i$1048576))

Any help on this conumdrum will greatly be appreciated. I have stared at this so long and am obviously out of my depth.

Thank you very much.

 

[TMCINNIS]

No. Right now my data is ~5000 rows by 60 columns. Thanks.

 

[Aladin Akyurek]

No. Right now my data is ~5000 rows by 60 columns. Thanks.

You should restrict the formulas to appropriate range sizes (If necessary, we can introduce dynamic named ranges.)...

Thusly:

=SUM(SUMIFS($N$3:$N$5000,$B$3:$B$5000,"MicrositeA",
    $D$3:$D$5000,{"*rebate*","*promotion*"}))

For collision-free summing, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH({"rebate","promotion"},$D$3:$D$5000)),
    IF($B$3:$B$5000="MicrositeA",MATCH($D$3:$D$5000,$D$3:$D$5000,0))),
    ROW($N$3:$N$5000)-ROW($N$3)+1),$N$3:$N$5000))

 

[TMCINNIS]

The following worked well.

I have never used control+shift+enter, not just enter so this is new to me. If I have this new formula in multiple cells measuring different conditions, will I have to use control+shift+enter in each cell, or can I mass execute all cells with the control+shift+enter combination? How do I mass execute control+shift+enter on multiple cells instead of doing them one by one?

Many many thanks.

 

[Aladin Akyurek]

The following worked well.

I have never used control+shift+enter, not just enter so this is new to me. If I have this new formula in multiple cells measuring different conditions, will I have to use control+shift+enter in each cell, or can I mass execute all cells with the control+shift+enter combination? How do I mass execute control+shift+enter on multiple cells instead of doing them one by one?

Many many thanks.

What is the sheet housing the data and what is the sheet in which the formula is located?

 

[TMCINNIS]

Sheet housing data = web_data
Sheet housing formula = prog_interest_rpt

Thanks

 

[Aladin Akyurek]

Sheet housing data = web_data
Sheet housing formula = prog_interest_rpt

Thanks

Thanks. One more: In which cell is the formula located in prog_interest_rpt?

 

[TMCINNIS]

Thanks. One more: In which cell is the formula located in prog_interest_rpt?

D3 to D20

I am measuring interest by key word and have several key words.

But the formula we have been testing is in cell D3

 

[Aladin Akyurek]

D3 to D20

I am measuring interest by key word and have several key words.

But the formula we have been testing is in cell D3

Consider the following set up with dynamic named ranges...

Activate Formulas | Name Manager from the ribbon.
Activate the New tab and enter Lrow in the Name box.
Enter the following formula in the Refers to box:

=MATCH(9.99999999999999E+307,web_data!$N:$N)

The foregoing procedure defines Lrow.

Define Brange (or something else more appropriate) as referring to:

=web_data!$B$3:INDEX(web_data!$B:$B,Lrow)

Define Drange (or something else more appropriate) as referring to:

=web_data!$D$3:INDEX(web_data!$D:$D,Lrow)

Define Nrange (or something else more appropriate) as referring to:

=web_data!$N$3:INDEX(web_data!$N:$N,Lrow)

Define Ivec as referring to:

=ROW(Nrange)-ROW(INDEX(Nrange,1,1)+1

Go to prog_interest_rpt sheet...

E3: MicrositeA

D3, control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH({"rebate","promotion"},Drange)),
    IF(Brange=$E3,MATCH(Drange,Drange,0))),Ivec),Nrange))

Drag down D3 up to D20.

 

[TMCINNIS]

Guys

Thanks for your help. I have been off for a few days. I just got a chance to look at what you sent me.

For the scope of what I am doing I have settled on the following two formulas that will work best.

For one variable in my search:

=SUMIFS($N$3:$N$5000,$B$3:$B$5000,"MicrositeA",$D$3:$D$5000,"*solar*")

For two variables in my search:

=SUMPRODUCT(($B$3:$B$5000="MicrositeA")*(MMULT(0+ISNUMBER(SEARCH({"rebate","solar"},$D$3:$D$5000)),{1;1})>0),$N$3:$N$5000)

Final two questions and I can put this string to bed..finally.

Based on what I will be using above for one variable, would the following be correct?
=AVERAGEIFS($N$3:$N$5000,$B$3:$B$5000,"MicrositeA",$D$3:$D$5000,"*solar*")

Based on what I will be using above for two variables, would the following be correct?
How do I correct this one for collision?
=AVERAGEIFS($N$3:$N$5000,$B$3:$B$5000,"MicrositeA",$D$3:$D$5000,"*Rebate*",$D$3:$D$5000,"*solar*")

Many thanks once more.

 

[Aladin Akyurek]

Guys

Thanks for your help. I have been off for a few days. I just got a chance to look at what you sent me.

For the scope of what I am doing I have settled on the following two formulas that will work best.

For one variable in my search:

=SUMIFS($N$3:$N$5000,$B$3:$B$5000,"MicrositeA",$D$3:$D$5000,"*solar*")

For two variables in my search:

=SUMPRODUCT(($B$3:$B$5000="MicrositeA")*(MMULT(0+ISNUMBER(SEARCH({"rebate","solar"},$D$3:$D$5000)),{1;1})>0),$N$3:$N$5000)

Final two questions and I can put this string to bed..finally.

Based on what I will be using above for one variable, would the following be correct?
=AVERAGEIFS($N$3:$N$5000,$B$3:$B$5000,"MicrositeA",$D$3:$D$5000,"*solar*")

Based on what I will be using above for two variables, would the following be correct?
How do I correct this one for collision?
=AVERAGEIFS($N$3:$N$5000,$B$3:$B$5000,"MicrositeA",$D$3:$D$5000,"*Rebate*",$D$3:$D$5000,"*solar*")

Many thanks once more.

This sums for any number of criteria collision-free:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH({"rebate","promotion"},Drange)),
IF(Brange=$E3,MATCH(Drange,Drange,0))),Ivec),Nrange))

This averages for any number of criteria collision-free:

=AVERAGE(IF(FREQUENCY(IF(ISNUMBER(SEARCH({"rebate","promotion"},Drange)),
IF(Brange=$E3,MATCH(Drange,Drange,0))),Ivec),Nrange))