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		Aged Outstanding
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>
</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:

<tbody>
</tbody>

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

<tbody>
</tbody>

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