SUMIFS Across Multiple Columns

[PFS12]

I have a formula that is getting out of hand. At first I found it to work without and issue but now I have to replicate it a little more frequently and its getting quite cumbesome to do so.

I have Columns L,M,N,O,P, and Q that all have individual Dollars that are pulled into this sheet at times. I have them summing based on the Month, and by the Salesperson. The month is tagged on manually by me in this sheet when I put information into it into column S, and the salemen information is in column E. The below is a snapshot of the sheet, starting in Column A with the Completion Date. and S being the Manually entered Month.

[TABLE="width: 2396"]
<tbody>[TR]
[TD]20190222[/TD]
[TD]CROPP019[/TD]
[TD]W06931[/TD]
[TD]03[/TD]
[TD]JRI[/TD]
[TD]306[/TD]
[TD]32[/TD]
[TD]$114.00[/TD]
[TD]3.12[/TD]
[TD]3.12[/TD]
[TD]0[/TD]
[TD]$300.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$135.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD][/TD]
[TD]February[/TD]
[/TR]
[TR]
[TD]20190222[/TD]
[TD]PANIP002[/TD]
[TD]W06991[/TD]
[TD]03[/TD]
[TD]JRI[/TD]
[TD]2149[/TD]
[TD]35[/TD]
[TD]$60.00[/TD]
[TD]13.25[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$720.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$360.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD][/TD]
[TD]February[/TD]
[/TR]
</tbody>[/TABLE]


I can't seem to figure out how to get the sheet to calculate across the multiple columns, and I am sure its an easy fix but, my question is how can I add the last three columns in each Row based on the criteria of the salesmen and the month? Currently I am adding like this, but need to add the last three columns in order to catch everything that I need. Cerrently I am doing it like this and there has to be a better way.

=SUMIFS('Service Dollars'!$L:$L,'Service Dollars'!$S:$S,"February",'Service Dollars'!$E:$E,"JRI")+SUMIFS('Service Dollars'!$M:$M,'Service Dollars'!$S:$S,"February",'Service Dollars'!$E:$E,"JRI")+SUMIFS('Service Dollars'!$N:$N,'Service Dollars'!$S:$S,"February",'Service Dollars'!$E:$E,"JRI")+SUMIFS('Service Dollars'!$O:$O,'Service Dollars'!$S:$S,"February",'Service Dollars'!$E:$E,"JRI")+SUMIFS('Service Dollars'!$P:$P,'Service Dollars'!$S:$S,"February",'Service Dollars'!$E:$E,"JRI")+SUMIFS('Service Dollars'!$Q:$Q,'Service Dollars'!$S:$S,"February",'Service Dollars'!$E:$E,"JRI")

Thank you in advanced and please reach out if this isn't making any sense.

Seth

 

[kweaver]

See if this works:

=SUMPRODUCT('Service Dollars'!$L:$Q*('Service Dollars'!$S:$S="February")*('Service Dollars'!$E:$E="JRI"))

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:59.88px;" /><col style="width:88.4px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:58.93px;" /><col style="width:29.47px;" /><col style="width:58.93px;" /><col style="width:24.71px;" /><col style="width:88.4px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >E</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td><td >W</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DATE</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">SALESPERSON</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DOLLARS</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DOLLARS</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DOLLARS</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DOLLARS</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DOLLARS</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">DOLLARS</td><td style="background-color:#92d050; font-weight:bold; "> </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">MONTH</td><td > </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">SALESPERSON</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">MONTH</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">RESULT</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">20190222</td><td >JRI</td><td style="text-align:right; ">300</td><td style="text-align:right; ">10</td><td style="text-align:right; ">0</td><td style="text-align:right; ">135</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td > </td><td >February</td><td > </td><td >JRI</td><td >February</td><td style="text-align:right; ">1540</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">20190222</td><td >JRI</td><td style="text-align:right; ">720</td><td style="text-align:right; ">15</td><td style="text-align:right; ">0</td><td style="text-align:right; ">360</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td > </td><td >February</td><td > </td><td >PFS</td><td >February</td><td style="text-align:right; ">4140</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">20190222</td><td >PFS</td><td style="text-align:right; ">1140</td><td style="text-align:right; ">20</td><td style="text-align:right; ">0</td><td style="text-align:right; ">585</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td > </td><td >February</td><td > </td><td >JRI</td><td >March</td><td style="text-align:right; ">6740</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">20190222</td><td >PFS</td><td style="text-align:right; ">1560</td><td style="text-align:right; ">25</td><td style="text-align:right; ">0</td><td style="text-align:right; ">810</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td > </td><td >February</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">20190222</td><td >JRI</td><td style="text-align:right; ">1980</td><td style="text-align:right; ">30</td><td style="text-align:right; ">0</td><td style="text-align:right; ">1035</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td > </td><td >March</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">20190222</td><td >JRI</td><td style="text-align:right; ">2400</td><td style="text-align:right; ">35</td><td style="text-align:right; ">0</td><td style="text-align:right; ">1260</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td > </td><td >March</td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >W2</td><td >=SUMPRODUCT(($E$2:$E$7=U2)*($S$2:$S$7=V2)*($L$2:$Q$7))</td></tr></table></td></tr></table>

 

[PFS12]

See if this works:

=SUMPRODUCT('Service Dollars'!$L:$Q*('Service Dollars'!$S:$S="February")*('Service Dollars'!$E:$E="JRI"))

This did not work

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[PFS12]

Try this

A

E

L

M

N

O

P

Q

R

S

T

U

V

W

JRI

February

JRI

February

JRI

February

PFS

February

PFS

February

JRI

March

PFS

February

JRI

March

JRI

March

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:59.88px;"><col style="width:88.4px;"><col style="width:58.93px;"><col style="width:58.93px;"><col style="width:58.93px;"><col style="width:58.93px;"><col style="width:58.93px;"><col style="width:58.93px;"><col style="width:29.47px;"><col style="width:58.93px;"><col style="width:24.71px;"><col style="width:88.4px;"><col style="width:76.04px;"><col style="width:76.04px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]DATE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]SALESPERSON[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]DOLLARS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]DOLLARS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]DOLLARS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]DOLLARS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]DOLLARS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]DOLLARS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]MONTH[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]SALESPERSON[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]MONTH[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: center"]RESULT[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]20190222[/TD]

[TD="align: right"]300[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]135[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]1540[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]20190222[/TD]

[TD="align: right"]720[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]4140[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]20190222[/TD]

[TD="align: right"]1140[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]585[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]6740[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]20190222[/TD]

[TD="align: right"]1560[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]810[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]20190222[/TD]

[TD="align: right"]1980[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1035[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]20190222[/TD]

[TD="align: right"]2400[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1260[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Cell Formula W2 =SUMPRODUCT(($E$2:$E$7=U2)*($S$2:$S$7=V2)*($L$2:$Q$7)) <tbody> </tbody>

<tbody>
</tbody>

Sorry for the delay as I am just now able to get back to this. This didn't work either. I tried several different ways with this formula, it either gets me an N/A result or #value . I tried adding a U,V, and W cloumn and mimiking what you did and I still can't make it work.

I do have this information across two sheets, Raw data comes in on Service Dollars and I pretty it up and Graph it on Service By Month. This is where I would like to add up all the raw data, but again I tried adding the columns that you suggested and I still can' tmake it work. I don't understand! Thank you though

 

[DanteAmor]

Sorry for the delay as I am just now able to get back to this. This didn't work either. I tried several different ways with this formula, it either gets me an N/A result or #value . I tried adding a U,V, and W cloumn and mimiking what you did and I still can't make it work.

I do have this information across two sheets, Raw data comes in on Service Dollars and I pretty it up and Graph it on Service By Month. This is where I would like to add up all the raw data, but again I tried adding the columns that you suggested and I still can' tmake it work. I don't understand! Thank you though

I attach my test file. With 2 examples, the formulas on the same sheet and the formulas on the sheet1

https://www.dropbox.com/s/6d4z6d4vrldgcbp/dollar.xlsx?dl=0

 

[PFS12]

I found that my issue was the columns needed to match in the search field, once I did that I think I can use this in the capacity you intended.

Thank you very much

 

[DanteAmor]

I found that my issue was the columns needed to match in the search field, once I did that I think I can use this in the capacity you intended.

Thank you very much

I'm glad to help you. Thanks for the feedback.