SUMIF with 1 range, 1 criteria, but multiple sum ranges

[TheIowaKid]

I have spent over an hour looking for a solution and cannot find one so I apologize if this has been answered before. It seems like it should have, but I couldnt find a solution posted. I want to use SUMIF with one range, one criteria which is text, and multiple sum ranges that are not adjacent to each other. Here is what I have tried to use, which of course will not work:

=SUMIF(B2:B21,"Fake Name",(E2:E21,H2:H21,N2:N21,T2:T21,Z2:Z21,AC2:AC21,AF2:AF21,AI2:AI21,AO2:AO21,AQ2:AQ21,AS2:AS21,AY2:AY21,BA2:BA21,BC2:BC21,BE2:BE21,BG2:BG21,BI2:BI21,BK2:BK21,AS2:AS21))

Here is a smaller scale example with the solution I tried and failed. Note that I dont want to do multiple SUMIFS as you can see there are A LOT of ranges.

SUMIF(A2:A6, "Fake Name",(B2:B6, D2:D6, E2:E6)) (this or course does not work, what will?)

Basically if A2:A6 contains the text "Fake Name" Then I would like to add the corresponding sum ranges.

Name Rejected NIGO Rejected IGO Rejected Mnt Rejected
Fake Name 8 5 3 2
Fake Name 6 3 7 0
Same Name 8 5 3 2
Fake Name 1 7 2 9
Same Name 8 5 3 2



Hopefully I have provided enough info here to get someone started! Excel is some fun stuff actually! Thanks for any help that can be provided.


EDIT: I noticed that my data was crunched but it should have 6 rows with 5 columns.

 

[markmzz]

Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Name</td><td style="text-align: center;;">Rejected NIGO</td><td style="text-align: center;;">Rejected IGO</td><td style="text-align: center;;">Rejected Mnt</td><td style="text-align: center;;">Rejected</td><td style="text-align: center;;"></td><td style="text-align: center;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Fake Name</td><td style="text-align: center;;">8</td><td style="text-align: center;;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;">38</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Fake Name</td><td style="text-align: center;;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">7</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Same Name</td><td style="text-align: center;;">8</td><td style="text-align: center;;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Fake Name</td><td style="text-align: center;;">1</td><td style="text-align: center;;">7</td><td style="text-align: center;;">2</td><td style="text-align: center;;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Same Name</td><td style="text-align: center;;">8</td><td style="text-align: center;;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">*******</td><td style=";">*******</td><td style=";">*******</td><td style=";">*******</td><td style=";">*******</td><td style=";">*******</td><td style=";">*******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A2:A6="Fake Name"</font>),B2:B6+D2:D6+E2:E6</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[TheIowaKid]

I tried this and it did not work for me. I tried it with my actual data, and I know that the result should give me 4. These are in columns T and AO for this particular sheet but they are not outputting to the result. Here is what I tried. I get no error, just no answer.

=SUMPRODUCT((B2:B21="Fake Name"),E2:E21+H2:H21+N2:N21+T2:T21+Z2:Z21+AC2:AC21+AF2:AF21+AI2:AI21+AO2:AO21+AQ2:AQ21+AS2:AS21+AY2:AY21+BA2:BA21+BC2:BC21+BE2:BE21+BG2:BG21+BI2:BI21+BK2:BK21+AS2:AS21)

 

[markmzz]

I tried this and it did not work for me. I tried it with my actual data, and I know that the result should give me 4. These are in columns T and AO for this particular sheet but they are not outputting to the result. Here is what I tried. I get no error, just no answer.

=SUMPRODUCT((B2:B21="Fake Name"),E2:E21+H2:H21+N2:N21+T2:T21+Z2:Z21+AC2:AC21+AF2:AF21+AI2:AI21+AO2:AO21+AQ2:AQ21+AS2:AS21+AY2:AY21+BA2:BA21+BC2:BC21+BE2:BE21+BG2:BG21+BI2:BI21+BK2:BK21+AS2:AS21)

Try this modification in your formula (in red --):

=SUMPRODUCT(--(B2:B21="Fake Name"),E2:E21+H2:H21+N2:N21+T2:T21+Z2:Z21+AC2:AC21+AF2:AF21+AI2:AI21+AO2:AO21+AQ2:AQ21+AS2:AS21+AY2:AY21+BA2:BA21+BC2:BC21+BE2:BE21+BG2:BG21+BI2:BI21+BK2:BK21+AS2:AS21)

Markmzz

 

[barry houdini]

Do all the columns that need to be summed have the same header (or some consistent data to distinguish those columns)? For example if all the columns to be summed have the header "rejected" then you can use this formula

=SUMPRODUCT((B2:B21="fake name")*(E1:AS1="rejected"),E2:AS21)

 

[TheIowaKid]

That worked! I have a follow up if you have a minute. What if I want it to give me the result if B2:B21 had a blank instead of text? So that it would only give me the results for a blank instead of Fake Name? Thanks again!

 

[TheIowaKid]

Nevermind. I found "" worked. Thanks again for all the help!

 

[Edward Sky]

Hey there, I have a similar issue and would love your advice. Here's the formula that won't work:

=SUMPRODUCT(--(sheet1!$F$9:$F$123=B4),sheet1!$BN$9:$BN$123-sheet1!$BM$9:$BM$123)

I don't know why I'm getting an error on this. is it because I'm referencing a different sheet?

 

[Jonmo1]

Check for Errors and/or TEXT values in either of the BN or BM ranges.

 

[Edward Sky]

hmm, the formulas in both BN and BM are timevalues:

In cell BN =IF(OR($G9="",$H9=""),"",TIMEVALUE(TEXT(H9,"00\:00")))
In cell BM =IF(OR($G9="",$H9=""),"",TIMEVALUE(TEXT(G9,"00\:00")))

would that be why?

Thanks heaps for the speedy reply!