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]

Aladin, Thanks for all your help. I am trying to make this work. My data set was slightly changed (what happens when I go out of town)(see below) and I updated your formula and named ranges as follows below. It returned a #NAME? error message. Can you let me know where I went wrong? I included the structure of my table and the named ranges below.

Here is what I did:

I was looking for the sum of all the page views where the key words "solar" "rebates" (col D) in the URL on the Web_Data page where the microsite name in col D was micrositeA on the corresponding line as the url with the key works.

I placed your formula (which I retooled to account for the changes) in page prog_interest_rpt cell C3.

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH({"rebate","Solar"},Drange)),
IF(Brange=$B1,MATCH(Drange,Drange,0))),Ivec),Erange))

The key words in cell B3 are "rebate solar" (not in quotes in cell).
The key words in cell B4 are "electric vehicles" (not in quotes in cell).
The key words in cell B4 are "green power" (not in quotes in cell).
Note: I have 56 lines of key words, both single and combinations.
I will want to repeat this formula on each of the lines for the corresponding key word/key word combinations.

Name Manger:
LROW =MATCH(9.99999999999999E+307,Web_Data!$E:$E)
Brange =Web_Data!$B$3:INDEX(Web_Data!$B:$B,Lrow)
Drange =Web_Data!$D$3:INDEX(Web_Data!$D:$D,Lrow)
Erange =Web_Data!$E$3:INDEX(Web_Data!$E:$E,Lrow)
IVEC =ROW(Nrange)-ROW(INDEX(Nrange,1,1))+1


Page name: Web_Data
Page structure:
COL A: PAGE NAME
COL B: MICROSITE NAME (we have 4 microsites)
COL C: CATEGORY TYPE
COL D: PAGE URL
COL E: PAGE VIEW COUNT Oct
COL F: PAGE VIEW COUNT Nov
COL G: PAGE VIEW COUNT Dec
COL H: PAGE VIEW COUNT Jan
ALL THE WAY TO COL N: FY TOTAL


Page name: prog_interest_rpt
Page structure:
COL A: PROGRAM NAME
COL B: KEY WORD, KEY WORDS (There are 50 lines of key words or key word combinations)
COL C: KEY WORD TOTAL Oct
COL D: KEY WORD TOTAL Nov
COL E: KEY WORD TOTAL Dec
COL F: KEY WORD TOTAL Jan
ALL THE WAY TO COL N: FY TOTAL

Again, thanks for your assistance. Coffee is on me if you ever come to Austin.

 

[TMCINNIS]

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

Aladdin ... I tried your suggestions. I had a problem. See my longer explanation of this problem earlier today. Thanks.

 

[Aladin Akyurek]

Aladdin ... I tried your suggestions. I had a problem. See my longer explanation of this problem earlier today. Thanks.

1) Must I check check whether you mapped the definitions correctly to your modified lay-out? I hope not. The most important thing is that Lrow is defined in reference to a numeric column, that is, Web_Data!$E:$E must be numeric.

2) Is B3 really a two word cell which is used as criterion/condition for Brange?

 

[TMCINNIS]

1) Must I check check whether you mapped the definitions correctly to your modified lay-out? I hope not. The most important thing is that Lrow is defined in reference to a numeric column, that is, Web_Data!$E:$E must be numeric.

2) Is B3 really a two word cell which is used as criterion/condition for Brange?

Aladin,
I got it to work. The problem was in the Ivec formula you sent me the other day...it was missing a parenthesis =ROW(Nrange)-ROW(INDEX(Nrange,1,1)+1. I corrected it to=ROW(Nrange)-ROW(INDEX(Nrange,1,1))+1 and then it worked.

Could I use AND & OR in the formula you provided?
Thanks again.
JT

 

[Aladin Akyurek]

Aladin,
I got it to work. The problem was in the Ivec formula you sent me the other day...it was missing a parenthesis =ROW(Nrange)-ROW(INDEX(Nrange,1,1)+1. I corrected it to=ROW(Nrange)-ROW(INDEX(Nrange,1,1))+1 and then it worked.

Great to hear that you have been able to catch that... and that it worked.

Could I use AND & OR in the formula you provided?
Thanks again.
JT

AND boils down to adding another IF at the appropiate point. OR is a bit different. Care to elaborate on both?

 

[TMCINNIS]

IE: If I wanted to sum where both "solar" AND "rebates" Occur in the same url.

If I wanted to sum where both "Residential" OR "commercial" Occur in the same url.

thank you.

 

[Aladin Akyurek]

IE: If I wanted to sum where both "solar" AND "rebates" Occur in the same url.

Assuming that the conditioning involves Drange...

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

If I wanted to sum where both "Residential" OR "commercial" Occur in the same url. [...]

Assuming that the conditioning involves Drange...

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("residential",Drange)+
    ISNUMBER(SEARCH("commercial",Drange)),IF(Brange=$E3,
    MATCH(Drange,Drange,0))),Ivec),Nrange))

Do these deliver what you expect?

 

[TMCINNIS]

I will test them tomorrow. Thanks.

 

[TMCINNIS]

Assuming that the conditioning involves Drange...

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

Assuming that the conditioning involves Drange...

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("residential",Drange)+
    ISNUMBER(SEARCH("commercial",Drange)),IF(Brange=$E3,
    MATCH(Drange,Drange,0))),Ivec),Nrange))

Do these deliver what you expect?

Aladin,

I tried both without success. I get two different error messages.

I get the error "you entered too many arguments for this function" for the following:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("residential",Drange)+
    ISNUMBER(SEARCH("commercial",Drange)),IF(Brange=$E3,
    MATCH(Drange,Drange,0))),Ivec),Nrange))

I get the error "the formula you entered contains an error" for the following:

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

I tried debugging them without success.

Many Thanks.

 

[Aladin Akyurek]

Aladin,

I tried both without success. I get two different error messages.

I get the error "you entered too many arguments for this function" for the following:

[...]

Would you post a scaled-down, representative sample along with the results that must obtain for that sample?