Conditional Sum or Database FUnction

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,
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,981
Office Version
  1. 365
Platform
  1. Windows
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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
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>
 

Kathleen0422

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

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920

ADVERTISEMENT

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>
 

Kathleen0422

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

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458

ADVERTISEMENT

Try this, change ranges to suit

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

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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....
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
Change the F1 to F2, the number of rows in each of the data ranges must be the same.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,336
Messages
5,601,023
Members
414,421
Latest member
tonybear1994

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
Top