double formula to return one result?

[gebo84]

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

 

[steve the fish]

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")

 

[FormR]

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

Hi, something like this maybe:

Excel 2013/2016
	B	C	AA	AB	AC	AD
1	Date		Lead Time		Month	Sum
2	01/01/2017	...Hidden Columns..	-9		01/04/2017	-9
3	20/01/2017		-7
4	08/02/2017		9
5	21/02/2017		7
6	10/03/2017		2
7	23/03/2017		-1
8	06/04/2017		-4
9	17/04/2017		-5
10	29/04/2017		8
11	10/05/2017		0
12	26/05/2017		9
13	08/06/2017		9
14	24/06/2017		-7
Sheet1

Cell Formulas
Range	Formula
AD2	=SUMIFS(AA:AA,B:B,">="&AC2,B:B,"<"&EOMONTH(AC2,0)+1,AA:AA,"<0")

 

[gebo84]

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")

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)

 

[gebo84]

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.

 

[gebo84]

Hi, something like this maybe:

Excel 2013/2016

	B	C	AA	AB	AC	AD
Date	Lead Time	Month	Sum

<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]

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

 

[FormR]

not sure this is what i need - your column AD should read "2" as there are 2 negative numbers

I did wonder if you wanted a count but you kept referring to wanting to returning a "sum".

 

[steve the fish]

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.

 

[gebo84]

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

 

[gebo84]

I did wonder if you wanted a count but you kept referring to wanting to returning a "sum".

Yeah sorry! it was a count that i needed