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.

 

[Aladin Akyurek]

Try...

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

 

[AhoyNC]

See if this works:

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

Aladins' formula would be better.

 

[TMCINNIS]

Try...

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

This worked outstandingly well. Many Thanks.

 

[Aladin Akyurek]

This worked outstandingly well. Many Thanks.

You are welcome.

 

[TMCINNIS]

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.

 

[Aladin Akyurek]

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.

 

[pgc01]

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)

 

[TMCINNIS]

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*")
[TABLE="width: 189"]
<tbody>[TR]
[TD="width: 189, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Aladin Akyurek]

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?