Another Countifs with multiple criteria and between 2 dates

[helenmeyer]

Hi again and thanks in advance for your help.

I'm trying to count all of a particular name that meets certain criteria. For example: i'm trying to count all "ASC" in coliumn B providing that column C has data in it but the date in column A need to fall between dates in two particular cells i.e. B6 and C6, C6 and D6, etc

This is the formula that I'm using cells b6, c6, d6, etc: =COUNTIFS(SP!$C:$C,"<>0",SP!$B:$B,B6,SP!$A:$A,"<=C4", SP!A:A,">=D4").

This is a copy of the table where I would like the information to come into:

1	A	B	C	D	E	F
2	01 April 2019
3
4		25/03/2019	18/03/2019	04/03/2019	18/02/2019	04/02/2019
5	Area	1 Wk	2+ Wks	4+ Wks	6+ Wks	8+ Wks
6	ASC	0	0	0	0	0
7	FACTORY
8	MESC - GENERAL SYSTEMS
9	MESC - AVIONICS
10	PROC AGE
11	MESC - SENSORS
12	Unassigned (all others)

<tbody>
[TD="colspan: 2"]Aged Outstanding
[/TD]

</tbody>

If I do a filter on the table below where C1 is not blank, and B equals ASC then in column B6 on the table above should be a 1 - but it's coming up as a zero.

This is a copy of the next tab where the information is coming from:

[TABLE="width: 461"]
<tbody>[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 461"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]RFQ Date
[/TD]
[TD]Pricing Area
[/TD]
[TD]Proposal Ref.
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]04/01/2019
[/TD]
[TD]MESC - SENSORS
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]07/01/2019
[/TD]
[TD]MESC - AVIONICS
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]07/01/2019
[/TD]
[TD]MESC - AVIONICS
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]07/01/2019
[/TD]
[TD]MESC - GENERAL SYSTEMS
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]11/01/2019
[/TD]
[TD]MESC - SENSORS
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]15/02/2019
[/TD]
[TD]ASC
[/TD]
[TD]STyC3357
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]15/02/2019
[/TD]
[TD]MESC - AVIONICS
[/TD]
[TD]STyC3347
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]15/02/2019
[/TD]
[TD]MESC - GENERAL SYSTEMS
[/TD]
[TD]STyC3351
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]15/02/2019
[/TD]
[TD]MESC - GENERAL SYSTEMS
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]15/02/2019
[/TD]
[TD]MESC - GENERAL SYSTEMS
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]15/02/2019
[/TD]
[TD]MESC - SENSORS
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]05/03/2019
[/TD]
[TD]FACTORY
[/TD]
[TD]STyC3387
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]05/03/2019
[/TD]
[TD]FACTORY
[/TD]
[TD]STyC3887
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]20/03/2019
[/TD]
[TD]NO DATA
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]20/03/2019
[/TD]
[TD]NO DATA
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]25/03/2019
[/TD]
[TD]ASC
[/TD]
[TD]STyC3375
[/TD]
[/TR]
</tbody>[/TABLE]


Hope that all makes sense.

As always I'm eternally grateful for any help.

H

 

[Joe4]

You put your C4 and D4 range references inside of double-quote, which treats them as literal strings "C4" and "D4", and not the values from cells C4 and D4.
If you want those date values to be used in your formula, it should look like this:

[COLOR=#ff0000]"<=" & C4[/COLOR]

and

[COLOR=#ff0000]">=" & D4[/COLOR]

 

[helenmeyer]

You put your C4 and D4 range references inside of double-quote, which treats them as literal strings "C4" and "D4", and not the values from cells C4 and D4.
If you want those date values to be used in your formula, it should look like this:

[COLOR=#ff0000]"<=" & C4[/COLOR]

and

[COLOR=#ff0000]">=" & D4[/COLOR]

I've tried what you said and I keep getting an error

 

[Joe4]

Please post your new formula attempt, exactly as you currently have it.

 

[helenmeyer]

Please post your new formula attempt, exactly as you currently have it.

This is what I did

=COUNTIFS(SP!$C:$C,"<>0",SP!$B:$B,B6,SP!$A:$A,">=" c4, SP!$A:$A, "<=" d4)

 

[Canapone]

Hi All,

a small fix

=COUNTIFS(SP!$C:$C,"<>0",SP!$B:$B,B6,SP!$A:$A,">="&c4, SP!$A:$A, "<="&d4)


Hope it helps

 

[Phill032]

Try this one, Columns A,B & C are where you are pulling all your data from. Copy this into B6 and drag accross and down..

=SUMPRODUCT(--($C$2:$C$17>0),--($B$2:$B$17=$A6),--($A$2:$A$17<=B$4),--($A$2:$A$17>=C$4))