I am trying to sum the $ amount earned by one individual within a cell date range. Please Please Help!!

[mcarver]

I am trying to sum the $ amount earned by one individual within a cell date range;


Employee Name Denomination Date
Aaron Christensen $5.00 June 1, 2011
Aaron Foster $1.00 June 10, 2011
Aaron Foster $1.00 June 10, 2011
Adam Vincent $1.00 May 31, 2011
Adam Vincent $1.00 May 31, 2011

Sum the total $ Aaron Foster earned within the cell date range of May 1, 2011 and May 31, 2011


**I have tried SUMIF and COUNTIF. It's a 3 part equation and I can only get a 2 part to work. I can not figure out how to get the cell date range to work Please Please help!!!!

 

[Joe4]

Welcome to the Board!

Since you are checking between two dates, you really have two criteria (plus one more for name), so SUMIF won't work.
Which version of Excel are you using? Excel 2007 and later has SUMIFS, which allows for multiple criteria.
If not, you can use SUMPRODUCT.

 

[markmzz]

Try 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 /><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><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Employee Name</td><td style=";">Denomination</td><td style="text-align: center;;">Date</td><td style="text-align: right;;"></td><td style=";">Start</td><td style=";">End</td><td style=";">Employee Name</td><td style=";">Total</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Aaron Christensen</td><td style="text-align: right;;">$5.00 </td><td style="text-align: center;;">1-Jun-11</td><td style="text-align: right;;"></td><td style="text-align: center;;">1-May-11</td><td style="text-align: center;;">31-May-11</td><td style=";">Adam Vincent</td><td style="text-align: right;;">$3.00 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Aaron Foster</td><td style="text-align: right;;">$4.00 </td><td style="text-align: center;;">1-May-11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Aaron Foster</td><td style="text-align: right;;">$4.00 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Aaron Foster</td><td style="text-align: right;;">$3.00 </td><td style="text-align: center;;">10-Jun-11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Aaron Christensen</td><td style="text-align: right;;">$0.00 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Adam Vincent</td><td style="text-align: right;;">$2.00 </td><td style="text-align: center;;">31-May-11</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;">6</td><td style=";">Adam Vincent</td><td style="text-align: right;;">$1.00 </td><td style="text-align: center;;">31-May-11</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;">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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*******</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">H2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A$2:A$6=G2</font>),--(<font color="Red">C$2:C$6<=F$2</font>),--(<font color="Red">$C2:$C6>=E$2</font>),B$2:B$6</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[mcarver]

Thanks markmzz, but what if the columns A, B and C are on multiple worksheets. for Example; Sheet 1- Accounting Sheet 2 - A/R. I tried adding the name of the file to the equation as Accounting!, but it didn't work.

=SUMPRODUCT(--(Accounting!A$3:Accounting!A$564=G15),--(Accounting!C$2:Accounting!C$564<=F$15),--(Accounting!$C2:Accounting!$C564>=E$15),Accounting!B$2:Accounting!B$564)

 

[T. Valko]

Thanks markmzz, but what if the columns A, B and C are on multiple worksheets. for Example; Sheet 1- Accounting Sheet 2 - A/R. I tried adding the name of the file to the equation as Accounting!, but it didn't work.

=SUMPRODUCT(--(Accounting!A$3:Accounting!A$564=G15),--(Accounting!C$2:Accounting!C$564<=F$15),--(Accounting!$C2:Accounting!$C564>=E$15),Accounting!B$2:Accounting!B$564)

What version of Excel are you using?

 

[mcarver]

Never mind. The < > in the original equation was backwards. I got it to work.

 

[T. Valko]

Never mind. The < > in the original equation was backwards. I got it to work.

Thanks markmzz, but what if the columns A, B and C are on multiple worksheets. for Example; Sheet 1- Accounting Sheet 2 - A/R. I tried adding the name of the file to the equation as Accounting!, but it didn't work.

=SUMPRODUCT(--(Accounting!A$3:Accounting!A$564=G15),--(Accounting!C$2:Accounting!C$564<=F$15),--(Accounting!$C2:Accounting!$C564>=E$15),Accounting!B$2:Accounting!B$564)

You don't need to repeat the sheet name for each range.

=SUMPRODUCT(--(Accounting!A$3:A$564=G15),--(Accounting!C$2:C$564<=F$15),--(Accounting!$C2:$C564>=E$15),Accounting!B$2:B$564)

If you're using Excel 2007 or later you'd want to use the SUMIFS function.

 

[markmzz]

Thanks markmzz, but what if the columns A, B and C are on multiple worksheets. for Example; Sheet 1- Accounting Sheet 2 - A/R. I tried adding the name of the file to the equation as Accounting!, but it didn't work.

=SUMPRODUCT(--(Accounting!A$3:Accounting!A$564=G15),--(Accounting!C$2:Accounting!C$564<=F$15),--(Accounting!$C2:Accounting!$C564>=E$15),Accounting!B$2:Accounting!B$564)

Mcarver,

Here is the two sheets and formula:

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Start</td><td style=";">End</td><td style=";">Employee Name</td><td style=";">Total</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">1-May-11</td><td style="text-align: center;;">31-May-11</td><td style=";">Adam Vincent</td><td style="text-align: right;;">$3.00 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Aaron Foster</td><td style="text-align: right;;">$4.00 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Aaron Christensen</td><td style="text-align: right;;">$0.00 </td></tr><tr ><td style="color: #161120;text-align: center;">5</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;">6</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;">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></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*******</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">Master</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">H2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">Accounting!A$2:A$6=G2</font>),--(<font color="Red">Accounting!C$2:C$6<=F$2</font>),--(<font color="Red">Accounting!C$2:C$6>=E$2</font>),Accounting!B$2:B$6</font>)</td></tr></tbody></table></td></tr></table><br /><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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Employee Name</td><td style=";">Denomination</td><td style="text-align: center;;">Date</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Aaron Christensen</td><td style="text-align: right;;">$5.00 </td><td style="text-align: center;;">1-Jun-11</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Aaron Foster</td><td style="text-align: right;;">$4.00 </td><td style="text-align: center;;">1-May-11</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Aaron Foster</td><td style="text-align: right;;">$3.00 </td><td style="text-align: center;;">10-Jun-11</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Adam Vincent</td><td style="text-align: right;;">$2.00 </td><td style="text-align: center;;">31-May-11</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Adam Vincent</td><td style="text-align: right;;">$1.00 </td><td style="text-align: center;;">31-May-11</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></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td></tr></tbody></table><p style="width: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">Accounting</p><br /><br />
Markmzz

 

[mcarver]

So what if I have the Master Sheet (sheet with equation) and the then 3 additional sheets that I need all of them included in the equation. For example; Master sheet (equation) and Accounting Sheet, A/R sheet, Customer Service Sheet.

 

[markmzz]

So what if I have the Master Sheet (sheet with equation) and the then 3 additional sheets that I need all of them included in the equation. For example; Master sheet (equation) and Accounting Sheet, A/R sheet, Customer Service Sheet.

Mcarver,

Could you post one small example of each sheet (Accounting, A/R and Customer Service) for can I see the data of each one?

Put borders around a small range of the cells for each sheet.

Then copy the range, and paste them here.

Like this:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Code</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Name01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>11111</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>Name02</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>22222</TD></TR></TBODY></TABLE>

Markmzz