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

TMCINNIS

New Member
Joined
Aug 26, 2015
Messages
29
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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:
Rich (BB code):

=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:
Rich (BB code):

=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))
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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:
Rich (BB code):

=MATCH(9.99999999999999E+307,web_data!$N:$N)
The foregoing procedure defines Lrow.

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

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

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

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

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

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

Define Ivec as referring to:
Rich (BB code):

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

Go to prog_interest_rpt sheet...

E3: MicrositeA

D3, control+shift+enter, not just enter:
Rich (BB code):
=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH({"rebate","promotion"},Drange)),
    IF(Brange=$E3,MATCH(Drange,Drange,0))),Ivec),Nrange))

Drag down D3 up to D20.
 
Upvote 0
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.
 
Upvote 0
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))

 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top