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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
See if this works:

Code:
=SUMPRODUCT(--($B$3:$B$1048576="MicrositeA"),--(ISNUMBER(SEARCH("Rebates",$I$3:$I$1048576))),S3:S1048576)

Aladins' formula would be better.
 
Last edited:
Upvote 0
You are welcome.

IF you would allow me one more question:

If I wanted to have the sum of two of these formulas added together with a different search valu as in what I have below, Would the following work?

=(SUMIFS($S$3:$S$1048576,$B$3:$B$1048576,"MicrositeA",$I$3:$I$1048576,"*Rebates*"))+(SUMIFS($S$3:$S$1048576,$B$3:$B$1048576,"MicrositeA",$I$3:$I$1048576,"*offerings*")))

Thanks.
 
Upvote 0
IF you would allow me one more question:

If I wanted to have the sum of two of these formulas added together with a different search valu as in what I have below, Would the following work?

=(SUMIFS($S$3:$S$1048576,$B$3:$B$1048576,"MicrositeA",$I$3:$I$1048576,"*Rebates*"))+(SUMIFS($S$3:$S$1048576,$B$3:$B$1048576,"MicrositeA",$I$3:$I$1048576,"*offerings*")))

Thanks.

That is:

=SUM(SUMIFS($S$3:$S$1048576,$B$3:$B$1048576,"MicrositeA",$I$3:$I$1048576,{"*rebates*","*offerings*"}))

Note that the sum so obtained can be inadmissible when both rebates and offerings occur in the same I-cell.

Avoiding such a collision is possible with...

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH({"rebates","offerings"},$I$3:$I$1048576)),
IF($B$3:$B$1048576="MicrositeA",MATCH($I$3:$I$1048576,$I$3:$I$1048576,0))),
ROW($S$3:$S$1048576)-ROW($S$3)+1),$S$3:$S$1048576))

which would be a time-consuming formula.
 
Upvote 0
Hi

Another option for the case of collisions:

=SUMPRODUCT(($B$3:$B$1048576="MicrositeA")*(MMULT(0+ISNUMBER(SEARCH({"rebates","offerings"},$I$3:$I$1048576)),{1;1})>0),$S$3:$S$1048576)
 
Upvote 0
Follow up:

I had to change my table structure:

Col B = Microsite
Col D = URLs
Col N = Total Page Views

This works well where there is one condition ("rebate") that I am looking for in column D.

=SUMIFS($N$3:$N$1048576,$B$3:$B$1048576,"MicrositeA",$D$3:$D$1048576,"*Rebate*")

<tbody>
</tbody>

This works well where there is two conditions ("rebate" and "offering") that I am looking for in column D.

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


Trying to solve the issue of collision:

The following one did not work. It returned an error message "Excel ran out of resources while attempting to calculate one or more formulas..."

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

The following one did not work. It returned an error message "Excel ran out of resources while attempting to calculate one or more formulas..."

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

I am interested in resolving the issue of collision. Where I can do this in the open workbook using SUMIF, IF there is an option using SUMPRODUCT where I can have this formula run outside a closed workbook, that would be optimal.

Thanks for all the help.
 
Upvote 0
Do you really have data up to 1048576th row or are you just expecting that it will be that much at some point in time?
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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