double formula to return one result?

gebo84

gebo84

Board Regular
Joined
Nov 6, 2014
Messages
96
Office Version
  1. 365
Platform
  1. Windows
I have a spreadsheet that is used to input incoming requests by date. a new row for each new requests received. in the same sheet in the last column i have some metrics that calculate how quickly the requests are dealt with etc.

i have a data collection sheet on a separate tab to collate the date and report on.

the data collection sheet is broken up into months Jan-Dec.

I need a formula that will look at the dates (in column B) and return a sum that is in column AA

column AA is a simple formula that that lets me see if we have responded to a request within the lead time and returns a number which represents days. a negative number will show we have responded quicker than the lead time and a positive number is how many days over the lead time we have gone.

The sum i need is the total requests below Zero for that month

is something like this possible? i imagine it will be a combination of two formulas but i cant seem to get it working

Thanks in advance
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
The formula is going to depend on what Jan-Dec actually are. Are they text? Are they true dates? Anyway ill assume text for now and see how it goes. Place 'Jan' in A1.

=COUNTIFS(B:B,">="&DATEVALUE(1&A1),B:B,"<"&EOMONTH(DATEVALUE(1&A1),0)+1,AA:AA,"<=0")
 
Upvote 0
gebo84 said:
I need a formula that will look at the dates (in column B) and return a sum that is in column AA
Click to expand...

Hi, something like this maybe:


Excel 2013/2016
BCAAABACAD
1DateLead TimeMonthSum
201/01/2017...Hidden Columns..-901/04/2017-9
320/01/2017-7
408/02/20179
521/02/20177
610/03/20172
723/03/2017-1
806/04/2017-4
917/04/2017-5
1029/04/20178
1110/05/20170
1226/05/20179
1308/06/20179
1424/06/2017-7
Sheet1
Cell Formulas
RangeFormula
AD2=SUMIFS(AA:AA,B:B,">="&AC2,B:B,"<"&EOMONTH(AC2,0)+1,AA:AA,"<0")
 
Upvote 0
steve the fish said:
The formula is going to depend on what Jan-Dec actually are. Are they text? Are they true dates? Anyway ill assume text for now and see how it goes. Place 'Jan' in A1.

=COUNTIFS(B:B,">="&DATEVALUE(1&A1),B:B,"<"&EOMONTH(DATEVALUE(1&A1),0)+1,AA:AA,"<=0")
Click to expand...

Thanks Steve, i still cant tailor this to my needs, ive tried playing about with it but i keep getting a zero as the returned value.

=COUNTIFS(Tracking!D2:D400,">="&DATEVALUE(1&B2),Tracking!D2:D400,"<"&EOMONTH(DATEVALUE(1&B2),0)+1,Tracking!AA2:AA400,"<=0")

This is what ive ended up with - the sheet I input the data into is called "Tracking" the day the requests are received are input into row B, row C calculates this date+ 10 days and this date becomes the due date, dates are input as 01-Nov-17 (text) i then have another column for completion date.
my examples in the first couple of rows should return a value of 2, because there are 2 negative numbers in row AA. (AA is a calculation of due date and completion dates and returns a number value if dates are equal, below or above the due date)


i hope this is making sense! im trying to give you a picture of what i am trying to achieve. My data collection is on a separate sheet looking into the "tracking sheet" (same workbook though)
 
Upvote 0
Sorry just to confuse matters - i know i have referenced cell D in the formula - thats because i have a hidden cell (for another formula i have) which returns the date as only text in 3 letters
it looks at cell B and returns NOV for example.
 
Upvote 0
FormR said:
Hi, something like this maybe:

Excel 2013/2016
BCAAABACAD
DateLead TimeMonthSum

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]01/01/2017[/TD]
[TD="align: center"]...Hidden Columns..[/TD]
[TD="align: right"]-9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/04/2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]-9[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]20/01/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]08/02/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]21/02/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]10/03/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]23/03/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]06/04/2017[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]-4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]17/04/2017[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]-5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]29/04/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10/05/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]26/05/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]08/06/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]24/06/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"]-7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AD2[/TH]
[TD="align: left"]=SUMIFS(AA:AA,B:B,">="&AC2,B:B,"<"&EOMONTH(AC2,0)+1,AA:AA,"<0")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Hi thanks for this, not sure this is what i need - your column AD should read "2" as there are 2 negative numbers for the month of Jan. that is what i am trying to achieve
 
Upvote 0
gebo84 said:
not sure this is what i need - your column AD should read "2" as there are 2 negative numbers
Click to expand...

I did wonder if you wanted a count but you kept referring to wanting to returning a "sum".
 
Upvote 0
Ok if im right you could just use:

=COUNTIFS(Tracking!$D$2:$D$400,"Jan",Tracking!$AA$2:$AA$400,"<=0")

"Jan" can be replaced by a cell reference containing Jan if you like.
 
Upvote 0
Works a treat! except its only returning a value = to zero i need it to return a value for all number less than zero? any ideas? ive a little play about with the <> signs but i either get a zero or 396 (396 would be correct as currently there is 396 cells with 0 in them) i should be getting a 2 returned
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Excel/Google Sheet Formula to extract client with unassigned Photographer
Replies
1
Views
200
Rhodie72
Rhodie72
ChrisMac1
  • Solved
Formula - Return text from a column if two other columns match
Replies
4
Views
174
Fluff
Fluff
J
  • Question Question
Formula to find date and return column number
Replies
2
Views
478
jc352
J
N
  • Solved
CF for summing a specific number of columns
Replies
6
Views
86
Peter_SSs
Peter_SSs
K
  • Solved
help with formatting a cell (for TIME, custom format) that retrieves its value from another cell...
Replies
2
Views
371
kbishop94
K

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

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