# Conditional Sum or Database FUnction

#### Kathleen0422

##### Board Regular
I am trying to write a formula that will look at the following data:

Column A --> TMID
Column B --> Cost Center
Column C --> TRC Code
Column D --> Name
Column E --> Date Worked
Column F --> Sum of Hours

My logic is:

If Name = Big Kahunna and TRC Code = COMPE or TRC Code = COMPM and Date Worked = 02/24/2009 give me the sum of the Hours

I can't get it to work, can someone help me please.

Thanks,

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
With your data to look up in row 2 try

=IF(AND(OR(C2="COMPE",C2="COMPM"),D2="BIG KAHUNNA",E2="02/24/2009"),F2,"")

Hope this helps

This shows two ways to get what you want.
With a varible number of multiple Codes acceptable, the DSUM might be more controlable.
(blue cells contribute to the sum)
<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=35><b>A</b><td align=center width=80><b>B</b><td align=center width=64><b>C</b><td align=center width=80><b>D</b><td align=center width=84><b>E</b><td align=center width=89><b>F</b><td align=center width=45><b>G</b><td align=center width=79><b>H</b><td align=center width=64><b>I</b><td align=center width=84><b>J</b>
<tr><td align=center bgcolor="#aaaaaa"><b>1</b><td bgcolor="#FFFFFF" > <FONT color="#000000">TMID</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Cost Center</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">TRC Code</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Name</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Date Worked</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Sum of Hours</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Name</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">TRC Code</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Date Worked</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">COMPE</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Big Kahunna</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2/24/09</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">15</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Big Kahunna</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">COMPE</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2/24/09</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>3</b><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">COMPM</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Big Kahunna</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2/24/09</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">16</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Big Kahunna</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">COMPM</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2/24/09</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>4</b><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">COMPM</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Moon Doggie</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2/24/09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">8</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>5</b><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">XXXX</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Big Kahunna</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2/24/09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">6</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>6</b><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">XXYX</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Mike Love</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">4/23/08</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">28</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">33</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>7</b><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">COMPM</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Big Kahunna</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">3/25/09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">13</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>8</b><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">COMPM</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Big Kahunna</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2/24/09</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFF99" > <FONT color="#000000">33</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr></table><table border=1 cellspacing = 0 bgcolor="#ddedcc">
<tr><td colspan=3 align="center">Formulas in this range: </tr>
<tr><td align=center>Range with same formula<td align=center>Cell:<td align=center>holds Formula:</tr>
<tr><td><td align=center>H6 <td align = left >=DSUM(A1:F8,"Sum of Hours",H1:J3)</tr>
<tr><td><td align=center>H8 <td align = left >=SUMPRODUCT(--(D2:D8="Big Kahunna"),--(C2:C8="COMPE")+(C2:C8="COMPM"),--(E2:E8="2/24/2009"+0),F2:F8)</tr></table>

Thanks for the response but I need for it to evaluate a range of approximately 25000 rows

in Column H I have a distinct listing of names and across the top I have the Dates

so Big Kahunna would be in H2, and the date of 2/24/2009 would be in column I

therefore I keyed in your formula and replaces your references to row 2 with \$1\$:\$25000 references and couldn't get it to work, can you take another quick look at this. I really do appreciate your help.

Something like this?
<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=35><b>A</b><td align=center width=80><b>B</b><td align=center width=64><b>C</b><td align=center width=80><b>D</b><td align=center width=84><b>E</b><td align=center width=89><b>F</b><td align=center width=45><b>G</b><td align=center width=79><b>H</b><td align=center width=64><b>I</b><td align=center width=84><b>J</b>
<tr><td align=center bgcolor="#aaaaaa"><b>1</b><td bgcolor="#FFFFFF" > <FONT color="#000000">TMID</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Cost Center</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">TRC Code</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Name</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Date Worked</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Sum of Hours</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">COMPE</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Big Kahunna</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2/24/09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">15</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Big Kahunna</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2/24/09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">33</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>3</b><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">COMPM</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Big Kahunna</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2/24/09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">16</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Big Kahunna</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">3/25/09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">13</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>4</b><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">COMPM</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Moon Doggie</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2/24/09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">8</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Moon Doggie</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2/24/09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">8</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>5</b><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">XXXX</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Big Kahunna</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2/24/09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">6</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Moon Doggie</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">3/25/09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">0</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>6</b><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">XXYX</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Mike Love</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">4/23/08</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">28</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>7</b><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">COMPM</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Big Kahunna</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">3/25/09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">13</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>8</b><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">xx</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">COMPM</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Big Kahunna</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2/24/09</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr></table><table border=1 cellspacing = 0 bgcolor="#ddedcc">
<tr><td colspan=3 align="center">Formulas in this range: </tr>
<tr><td align=center>Range with same formula<td align=center>Cell:<td align=center>holds Formula:</tr>
<tr><td>J2:J5<td align=center>J2 <td align = left >=SUMPRODUCT(--(\$D\$2:\$D\$8=H2),--(\$E\$2:\$E\$8=I2),--(\$C\$2:\$C\$8="COMPE")+(\$C\$2:\$C\$8="COMPM"),\$F\$2:\$F\$8)</tr></table>

i keyed in:

=(SUMPRODUCT(--(\$D\$2:\$D\$25000=\$H2),--(\$C\$2:\$C\$25000="COMPE")+(\$C\$2:\$C\$25000="COMPM"),--(\$E\$2:\$E\$25000=I\$1),\$F\$1:\$F\$25000)

I am receiving a #VALUE result,

Thanks for your info as well, any ideals

Try this, change ranges to suit

=SUMPRODUCT(--(D2:D100="Big Kahuna"),--ISNUMBER(MATCH(C2:C100,{"code1","code2"},0)),--(F2:F100=L1),G2:G100)

i keyed in:

=(SUMPRODUCT(--(\$D\$2:\$D\$25000=\$H2),--(\$C\$2:\$C\$25000="COMPE")+(\$C\$2:\$C\$25000="COMPM"),--(\$E\$2:\$E\$25000=I\$1),\$F\$1:\$F\$25000)

I am receiving a #VALUE result,

Thanks for your info as well, any ideals

On Mickerson's one, you have the wrong range to sum....

Change the F1 to F2, the number of rows in each of the data ranges must be the same.

Thank you all. Up and running again.

Replies
6
Views
140
Replies
2
Views
180
Replies
5
Views
150
Replies
0
Views
86
Replies
0
Views
92

1,203,052
Messages
6,053,225
Members
444,648
Latest member
sinkuan85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back