Another Sum based on multiple criteria problem

[AFST]

Hey Folks,

I'm having a major issue trying to find a sum based on multiple other criteria. Here's an example of what the speadsheet would look like:

<table border="0" cellpadding="0" cellspacing="0" height="121" width="478"><colgroup><col style="mso-width-source:userset;mso-width-alt:5010;width:103pt" width="137"> <col style="width:48pt" span="4" width="64"> </colgroup><tbody><tr style="height:16.5pt" height="22"> <td class="xl65" style="height:16.5pt;width:103pt" height="22" width="137">Date/Time</td> <td class="xl66" style="border-left:none;width:48pt" width="64">Hours</td> <td class="xl66" style="border-left:none;width:48pt" width="64">FRS</td> <td class="xl66" style="border-left:none;width:48pt" width="64">ACM1</td> <td class="xl67" style="border-left:none;width:48pt" width="64">ACM2</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;border-top:none" height="21">01-Jul-11 3:36 PM</td> <td class="xl64" style="border-top:none;border-left:none">5.9</td> <td class="xl69" style="border-top:none;border-left:none">Bob
</td> <td class="xl70" style="border-top:none;border-left:none"> </td> <td class="xl71" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;border-top:none" height="20">02-Jul-11 7:21 PM</td> <td class="xl64" style="border-top:none;border-left:none">3.6</td> <td class="xl69" style="border-top:none;border-left:none">John</td> <td class="xl70" style="border-top:none;border-left:none"> </td> <td class="xl71" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;border-top:none" height="20">03-Jul-11 11:00 AM</td> <td class="xl64" style="border-top:none;border-left:none">7.3</td> <td class="xl69" style="border-top:none;border-left:none">Bob</td> <td class="xl70" style="border-top:none;border-left:none"> </td> <td class="xl71" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt;border-top:none" height="20">04-Jul-11 8:00 AM</td> <td class="xl64" style="border-top:none;border-left:none">5.5</td> <td class="xl69" style="border-top:none;border-left:none">John</td> <td class="xl70" style="border-top:none;border-left:none"> </td> <td class="xl71" style="border-top:none;border-left:none"> </td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl72" style="height:15.75pt;border-top:none" height="21">06-Jul-11 9:12 AM</td> <td class="xl73" style="border-top:none;border-left:none">5.6</td> <td class="xl74" style="border-top:none;border-left:none">Bob</td> <td class="xl74" style="border-top:none;border-left:none">John</td> <td class="xl75" style="border-top:none;border-left:none"> </td> </tr> </tbody></table>
Here's a criteria list: 1) it needs to evaluate the "Date/Time" column to see if its within 30 days of today's date. 2) It needs to check the FRS and each ACM column for "John". The same name would never repeat twice in the same row (i.e. wouldn't be in the FRS and one of the ACM columns). If the criteria was met it would sum up the total in the "Hours" column for that person.

Now the other catch, and I really have no idea if this has any affect or not (but it shouldn't) is the cell that returns the result is on a different worksheet in the same workbook. Also each month of the year has its own worksheet and because one of the criteria checks within 30 days of today, that means its usually over 2 months aka 2 worksheets. I'm thinking that can just be easily solved with a + and a repeat of the formula with the new worksheet name in it.

This is the equation I've been trying to use with no luck so far just a #VALUE! error:

=SUMPRODUCT(--('July 11'!I15:R41>=(TODAY()-30)),--('July 11'!BJ15:BM41="John"),--('July 11'!I15:R41))

July 11 is obviously the worksheet name and the reference cells are there. Does it matter if they are merged or not?

I'm lost and any help is appreciated.

 

[Domenic]

Let's assume that A1:E6 contains...

<TABLE style="WIDTH: 289pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=385><COLGROUP><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><COL style="WIDTH: 48pt" span=4 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: 97pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=129>Date/Time</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=xl63 width=64>Hours</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=xl63 width=64>FRS</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=xl63 width=64>ACM1</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=xl63 width=64>ACM2</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; WIDTH: 97pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=129 align=right>7/01/11 15:36</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>5.9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Bob</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </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; WIDTH: 97pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=129 align=right>7/02/11 19:21</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>3.6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>John</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </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; WIDTH: 97pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=129 align=right>7/03/11 11:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>7.3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Bob</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </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; WIDTH: 97pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=129 align=right>7/04/11 8:00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>5.5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>John</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </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; WIDTH: 97pt; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=129 align=right>7/06/11 9:12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>5.6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Bob</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>John</SPAN></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=xl64> </TD></TR></TBODY></TABLE>

Since the name of interest occurs only once in any row, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(A2:A6>=TODAY()-30,IF($C$2:$E$6="John",$B$2:$B$6)))

 

[AFST]

That's perfect it spits out the result I'm looking for but...

Is there a way to do it without using that Ctrl+Shift+Enter method. The way I've set up the worksheet has merged cells that are used as the reference and the method you proposed doesn't respond well to merged cells. As an example in one of my worksheets the "Date/Time" column spans from A15:H45.

I realize I could go through each worksheet and change all of the merged columns into a single column but based on the amount of data I have and the many other formulas I've used in the workbook it would be a massive undertaking I would like to try and avoid.

 

[Domenic]

While A15:H45 are merged, reference A15:A45 in the formula instead. Otherwise, the following less efficient formula can be used...

=SUMPRODUCT((A2:A6>=TODAY()-30)*($C$2:$E$6="John")*($B$2:$B$6))

 

[AFST]

I appreciate all the help so far but I'm still having a few difficulties.

1) For whatever reason I can't get the formula to work across worksheets. In my particular case I want the result displayed in a cell on the worksheet named "Crew Hours" while the formula is referencing cell ranges on a worksheet named "June 11" and another "July 11" etc. All of the ranges and criteria in the formula would stay the same its just the name of the worksheet that changes. Would it look something like:

=SUMPRODUCT(('June 11'!E4:E11>=TODAY()-30)*('June 11'!$G$4:$I$8="John")*('June 11'!$F$4:$F$8))+SUMPRODUCT(('July 11'!E4:E11>=TODAY()-30)*('July 11'!$G$4:$I$8="John")*('June 11'!$F$4:$F$8)) etc. for the additional months

That returns a #N/A message. Also note the ranges in that sample are single, not merged, columns which brings us to problem 2...

2) Even when I use the sumproduct formula on the same worksheet over the merge cells I'm not getting anywhere. No rows are merged. Here's the layout:

"Date/Time" is one merged column of columns A:H
"Hours" is one merged column of columns I:R
And then there's a series a columns spanning from BB:CG. These columns are where the names are checked and again no same name appears in the same row. Each smaller column from BB:CG is f4 columns wide so BB:BE,BF:BI,BJ:BM etc.

The range of the rows I want to evaluate on each worksheet goes from 15:60

I made this way more difficult then it needs to be but this is the last thing holding me back from finishing it. I just need a way to answer both problems with one solution and hopefully it exists.

Thanks for the help.

 

[Domenic]

The number of rows referenced in each range needs to be the same. Try...

=SUMPRODUCT(('June 11'!$E$4:$E$11>=TODAY()-30)*('June 11'!$G$4:$I$11="John")*('June 11'!$F$4:$F$11))+SUMPRODUCT(('July 11'!$E$4:$E$11>=TODAY()-30)*('July 11'!$G$4:$I$11="John")*('June 11'!$F$4:$F$11))

 

[AFST]

Ah silly error to make. I tried that sample you provided and it produces the result from the June 11 worksheet but its not adding the result from the July 11 worksheet??

Any tips on how to deal with problem 2 from my previous post?

 

[Domenic]

Ah silly error to make. I tried that sample you provided and it produces the result from the June 11 worksheet but its not adding the result from the July 11 worksheet??

I didn't notice earlier, but the worksheet being referenced for the last range in the second formula is incorrect. Try...

=SUMPRODUCT(('June 11'!$E$4:$E$11>=TODAY()-30)*('June 11'!$G$4:$I$11="John")*('June 11'!$F$4:$F$11))+SUMPRODUCT(('July 11'!$E$4:$E$11>=TODAY()-30)*('July 11'!$G$4:$I$11="John")*('July 11'!$F$4:$F$11))

"Date/Time" is one merged column of columns A:H
"Hours" is one merged column of columns I:R
And then there's a series a columns spanning from BB:CG. These columns are where the names are checked and again no same name appears in the same row. Each smaller column from BB:CG is f4 columns wide so BB:BE,BF:BI,BJ:BM etc.

The range of the rows I want to evaluate on each worksheet goes from 15:60

In this case, you'll need to reference BB:CG...

=SUMPRODUCT(($A$15:$A$60>=TODAY()-30)*($BB$15:$CG$60="John")*($I$15:$I$60))

 

[AFST]

I've worked with all the info you've given me and I now have it doing exactly what I need to. Thanks for the help.

 

[Domenic]

You're very welcome! Glad I could help!