Problem with Logical Operators with Cell Reference in SUMIFS()

D

dross

New Member
Joined
Dec 19, 2010
Messages
27
I use Excel 2011 and Mac OS X (10.6)

In a Worksheet named FundedResearch I have a table. In named table columns D and R respectively are dates and dollar amounts as follows:

<table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" height="309" width="87"><tbody><tr style="mso-height-source:userset;height:73.0pt" height="73"><td class="xl64" style="height:73.0pt;width:71pt;font-size: 12.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:none;border-right: .5pt solid white;border-bottom:1.5pt solid white;border-left:.5pt solid white; background:#4F81BD;mso-pattern:#4F81BD none" height="73" width="71">Actual / Anticipated Award Date</td><td valign="top">
</td> </tr> <tr style="mso-height-source:userset;height:15.0pt" height="15"> <td class="xl68" style="height:15.0pt;width:71pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#B8CCE4;mso-pattern:#B8CCE4 none" height="15" width="71">08/01/11</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl69" style="height:15.0pt;width:71pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="15" width="71">08/01/11</td><td valign="top">
</td> </tr> <tr style="mso-height-source:userset;height:15.0pt" height="15"> <td class="xl68" style="height:15.0pt;width:71pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#B8CCE4;mso-pattern:#B8CCE4 none" height="15" width="71">08/01/11</td><td valign="top">
</td> </tr> <tr style="mso-height-source:userset;height:15.0pt" height="15"> <td class="xl68" style="height:15.0pt;width:71pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="15" width="71">02/01/11</td><td valign="top">
</td> </tr> <tr style="mso-height-source:userset;height:15.0pt" height="15"> <td class="xl68" style="height:15.0pt;width:71pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#B8CCE4;mso-pattern:#B8CCE4 none" height="15" width="71">11/01/11</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl69" style="height:15.0pt;width:71pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="15" width="71">11/01/11</td><td valign="top">
</td> </tr> <tr style="mso-height-source:userset;height:15.0pt" height="15"> <td class="xl68" style="height:15.0pt;width:71pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#B8CCE4;mso-pattern:#B8CCE4 none" height="15" width="71">11/01/11</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl69" style="height:15.0pt;width:71pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="15" width="71">12/15/11</td><td valign="top">
</td> </tr> <tr style="mso-height-source:userset;height:31.0pt" height="31"> <td class="xl68" style="height:31.0pt;width:71pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid white; border-right:.5pt solid white;border-bottom:none;border-left:.5pt solid white; background:#B8CCE4;mso-pattern:#B8CCE4 none" height="31" width="71">08/15/11</td><td valign="top">
</td> </tr> </tbody></table>

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .25in 1.0in .25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-page-orientation:landscape; mso-horizontal-page-align:center;} td {padding:0px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl64 {font-weight:700; text-align:center; vertical-align:middle; white-space:normal;} .xl65 {white-space:normal;} .xl66 {vertical-align:middle; white-space:normal;} .xl67 {mso-number-format:"\0022$\0022\#\,\#\#0_\)\;\[Red\]\\\(\0022$\0022\#\,\#\#0\\\)"; text-align:right; vertical-align:middle; white-space:normal;} --> </style> <table style="border-collapse: collapse;width:65pt" border="0" cellpadding="0" cellspacing="0" width="65"> <colgroup><col style="mso-width-source:userset;mso-width-alt:2773;width:65pt" width="65"> </colgroup><tbody><tr style="mso-height-source:userset;height:73.0pt" height="73"> <td class="xl64" style="height:73.0pt;width:65pt;font-size: 12.0pt;color:white;font-weight:700;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:none;border-right: .5pt solid white;border-bottom:1.5pt solid white;border-left:.5pt solid white; background:#4F81BD;mso-pattern:#4F81BD none" height="73" width="65">Fall 2011</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="mso-height-source:userset;height:15.0pt" height="15"> <td class="xl67" style="height:15.0pt;width:65pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#B8CCE4;mso-pattern:#B8CCE4 none" height="15" width="65">$12,115 </td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl67" style="height:15.0pt;width:65pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="15" width="65">$5,769 </td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="mso-height-source:userset;height:15.0pt" height="15"> <td class="xl67" style="height:15.0pt;width:65pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#B8CCE4;mso-pattern:#B8CCE4 none" height="15" width="65">$48,649 </td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="mso-height-source:userset;height:15.0pt" height="15"> <td class="xl67" style="height:15.0pt;width:65pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="15" width="65">$74,556 </td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="mso-height-source:userset;height:15.0pt" height="15"> <td class="xl67" style="height:15.0pt;width:65pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#B8CCE4;mso-pattern:#B8CCE4 none" height="15" width="65">$879 </td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl67" style="height:15.0pt;width:65pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="15" width="65">$879 </td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="mso-height-source:userset;height:15.0pt" height="15"> <td class="xl67" style="height:15.0pt;width:65pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#B8CCE4;mso-pattern:#B8CCE4 none" height="15" width="65">$879 </td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl67" style="height:15.0pt;width:65pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border:.5pt solid white; background:#DCE6F1;mso-pattern:#DCE6F1 none" height="15" width="65">$175 </td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="mso-height-source:userset;height:31.0pt" height="31"> <td class="xl67" style="height:31.0pt;width:65pt;font-size: 12.0pt;color:black;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:Calibri;border-top:.5pt solid white; border-right:.5pt solid white;border-bottom:none;border-left:.5pt solid white; background:#B8CCE4;mso-pattern:#B8CCE4 none" height="31" width="65">$7,784 </td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> </tbody></table>

In M4 of a Worksheet named Parameters is a Beginning of Period date with the value 08/15/11.

I am trying to Sum the dollar amounts in the Fall column for projects whose award date was on or after the Beginning of Period Date and for which dollars were expended during the Fall, using the SUMIFS() function.

If I hard code the Beginning of Period date as follows, I get a correct answer of $10,596:

=SUMIFS(FundedResearch[Fall 2011],FundedResearch[Actual / Anticipated Award Date],">=08/15/11",FundedResearch[Fall 2011],">0")

However, if I replace the hard-coded date with the cell reference to the Beginning of Period: ">=Parameters!M4" the formula returns a zero, and if I remove the quotes from around the cell reference: >=Parameters!M4 I get an error. I need to use the cell reference as the date may change.

What am I missing?


<table style="border-collapse: collapse; width: 38px; height: 249px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="mso-height-source:userset;height:73.0pt" height="73"><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr style="mso-height-source:userset;height:15.0pt" height="15"><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr style="height:15.0pt" height="15"><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr style="mso-height-source:userset;height:15.0pt" height="15"><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr style="mso-height-source:userset;height:15.0pt" height="15"><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr style="mso-height-source:userset;height:15.0pt" height="15"><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr style="height:15.0pt" height="15"><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr style="mso-height-source:userset;height:15.0pt" height="15"><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr style="height:15.0pt" height="15"><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr><tr style="mso-height-source:userset;height:31.0pt" height="31"><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td> </tr> </tbody></table>
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Try

=SUMIFS(FundedResearch[Fall 2011],FundedResearch[Actual / Anticipated Award Date],">="&Parameters!M4,FundedResearch[Fall 2011],">0")


Hope that helps.
 
Upvote 0
Hi I have a very similar problem however the above solution does not appear to be working.

I have a very large table containing sales transactions covering two years range from 201901 to 202007 containing circa 195K lines. The data is contained within a table called "Shipments".

I can quite easily use a sumifs function to extract and sum data from the table for a specific month e.g. 201907. Cell F154 contained the date 201907 e.g.

=SUMIFS(Shipments[SHIPMENT_VALUE_EURO],Shipments[YR_MONTH],$F$154,Shipments[subregion],B156).

The same function also works correctly if I manually enter the date e.g.

=SUMIFS(Shipments[SHIPMENT_VALUE_EURO],Shipments[YR_MONTH],"201907",Shipments[subregion],B156).

However, I'd now like to sum over a larger range, from 201901 to 201907.

I have tried to use the following function, along the lines of the solution proposed above but the function return "0", which cannot possibly be the answer

=SUMIFS(Shipments[SHIPMENT_VALUE_EURO],Shipments[YR_MONTH],"<="&F154,Shipments[subregion],B156).

I have also unsuccessfully tried:

=SUMIFS(Shipments[SHIPMENT_VALUE_EURO],Shipments[YR_MONTH],"<=201907",Shipments[subregion],B156).

PLease can someone explain what I'm doing wrong, please?

I'm assuming there is an error in the function I'm using?

Or alternatively is the issue to do with the fact that I'm referencing a table?

I hope I have provide enough information.
 
Upvote 0
Apologies I missed off which version of excel (Excel 2016) and the operating system (Windows 10).

Also forgot to mention cell B156 has UK in it.

Here is a small subset of the data:

Book1
BCD
2SHIPMENT_VALUE_EUROYR_MONTHsubregion
3296201909France
4165202001Belgium
5-5201911UK
6-105201901UK
7652201903UK
8265201906Scandinavia
9100201901Netherlands
10158201,907Scandinavia
11236202003UK
12376201909Scandinavia
13190201901Netherlands
14588201903France
15287202003France
16433202002France
17106202007Finland & Baltic
18738201905Scandinavia
19443201906Finland & Baltic
2039202007France
21-695202003UK
221,178202006Russia
2322202006Scandinavia
24547202003Russia
25382202002Finland & Baltic
2692202004Spain & Portugal
27321201901France
28562201906UK
Sheet1
 
Upvote 0
Apologies for adding further to the message string but I’ve solved the issue and thought I’d post in case anyone else has the same issue.

The original formula:

=SUMIFS(Shipments[SHIPMENT_VALUE_EURO],Shipments[YR_MONTH],$F$154,Shipments[subregion],B156).

where F154 is the date 201907

Or

=SUMIFS(Shipments[SHIPMENT_VALUE_EURO],Shipments[YR_MONTH],"201907",Shipments[subregion],B156).

Both formula work irrespective of the data format of the lookup date or the format of the data in column [YR_MONTH].

However when extending this to a logic function I.e.

=SUMIFS(Shipments[SHIPMENT_VALUE_EURO],Shipments[YR_MONTH],"<=201907",Shipments[subregion],B156).

The data in Column [YR_MONTH] needs to be formatted as a “Number”. If dates are entered into the column as Text the function returns “0”.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
C
  • Question Question
Picture won't paste in cell overlay
Replies
0
Views
249
Cerka
C
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
Adding a custom message.
Replies
1
Views
659
Micron
M

Forum statistics

Threads
1,224,521
Messages
6,179,282
Members
452,902
Latest member
Knuddeluff

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