Conditional Sum or Database FUnction

K

Kathleen0422

Board Regular
Joined
Apr 12, 2006
Messages
188
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
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
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
Try this, change ranges to suit

=SUMPRODUCT(--(D2:D100="Big Kahuna"),--ISNUMBER(MATCH(C2:C100,{"code1","code2"},0)),--(F2:F100=L1),G2:G100)
 
Upvote 0
Kathleen0422 said:
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
Click to expand...

On Mickerson's one, you have the wrong range to sum....
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Referencing whole row number range by Formula result
Replies
1
Views
112
KRice
K
S
  • Solved
SUMIFS Statement
Replies
6
Views
304
ssmith3156
S
T
  • Question
Sum the values from the column for a specific period which matches values from another column corresponding to that specific period
Replies
2
Views
162
thetnaingsoe
T
M
  • Solved
Filter function condition ideas?
Replies
5
Views
231
KRice
K
R
  • Solved
Loop trough list by year and month and sum value into textbox
Replies
5
Views
419
NoSparks
N

Forum statistics

Threads
1,214,379
Messages
6,119,190
Members
448,874
Latest member
Lancelots

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top