Countifs formula

[Mattlake]

Hi

After two days and no hair left (I didn't want to jump straight to the forum) I am in need of your help once more.

I have a spreadsheet that is totalling up fields from another tab.

What I am looking to do is to count up column if it has certain text in and with in a date range.

the formula I have got to is =COUNTIFS('2022'!E:E,A3,'2022'!F:F,D24:E24) but this is showing 0

any help would be so appreciated

Regards
Matthew

 

[Mortiray]

Breaking down your formula gives me

=COUNTIFS(
criteria_range1 = '2022'!E:E
criteria1 = A3
criteria_range2 = '2022'!F:F
criteria2 = D24:E24
)

Why is your second criteria a range of multiple cells? I don't think the COUNTIFS function can handle multiple cells as a criteria.

 

[Fluff]

How about

=sumproduct(COUNTIFS('2022'!E:E,A3,'2022'!F:F,D24:E24))

 

[JamesCanale]

MrExcelPlayground13.xlsx
	A	B	C	D	E	F
1	2				Fred	11/8/2022
2					Jamie	11/8/2022
3	Jamie				Harry	11/9/2022
4	11/9/2022				Mary	11/10/2022
5	11/20/2022				Sue	11/11/2022
6					Fred	11/12/2022
7					Fred	11/13/2022
8					Jamie	11/14/2022
9					Harry	11/15/2022
10					Mary	11/16/2022
11					Mary	11/17/2022
12					Sue	11/18/2022
13					Jamie	11/19/2022
14					Fred	11/20/2022
15					Jamie	11/21/2022
Sheet25

Cell Formulas
Range	Formula
A1	A1	=COUNTIFS(E1:E14,A3,F1:F14,">="&A4,F1:F14,"<="&A5)
F3:F15	F3	=F2+1

 

[ExceLoki]

try this

=SUMPRODUCT(--('2022'!F:F>=D24), --('2022'!F:F<=E24), --('2022'!E:E=A3))

 

[Mattlake]

how do you you keep this all in your heads!!!!

Thank you that worked.

The D24 - E24 were the date range

Regards

Matthew

 

[Fluff]

If D24 & E24 are the dates, I would suggest

=COUNTIFS('2022'!E:E,A3,'2022'!F:F,">="&D24,'2022'!F:F,"<="&E24)

it will be more efficient than sumproduct, especially when you whole column references.