Counting with multiple criteria

[ps50150]

Hi,


Column AT has dates in date format (1-Jun-2018, 2-Jun-2018, etc until end 30-Jun-2018)
Column AU has dates (date format) but display is weekday format (ie Mon, Tues, Wed..)
Column AV has numbers on certain days for example 1 Jun 2018 could have 3, and 17 Jun 2018 could have another 3 etc, some dates could be blank...

I need a formula that counts how many Mondays have a value in column AV,
Then how many Tuesdays have a value in column AV

=SUMPRODUCT(--(WEEKDAY(AX$7:AX$37)=AZ43),--(AZ7:AZ37>0))

AZ43 = 1 (eg Monday)

Don't know why its not working any help is very much appreciated!

 

[ps50150]

Sorry the formula I am trying is:
=SUMPRODUCT(--(WEEKDAY(AT$7:AT$37)=AV43),--(AV7:AV37>0))

 

[jtakw]

Hi,


Column AT has dates in date format (1-Jun-2018, 2-Jun-2018, etc until end 30-Jun-2018)
Column AU has dates (date format) but display is weekday format (ie Mon, Tues, Wed..)
Column AV has numbers on certain days for example 1 Jun 2018 could have 3, and 17 Jun 2018 could have another 3 etc, some dates could be blank...

I need a formula that counts how many Mondays have a value in column AV,
Then how many Tuesdays have a value in column AV

=SUMPRODUCT(--(WEEKDAY(AX$7:AX$37)=AZ43),--(AZ7:AZ37>0))

AZ43 = 1 (eg Monday)

Don't know why its not working any help is very much appreciated!

Hi,

Your description says you want the COUNT, not SUM of the values in Column AV, 2 ways:
AX7 formula does not need a cell reference for WEEKDAY,
AX8 formula uses a cell reference for WEEKDAY,

Both formulae copied across, might need tweaking depending on your layout.

Book1
	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD
6					Mon	Tue	Wed	Thu	Fri	Sat	Sun
7	1-Jun-18	Fri	3		1	0	0	0	2	0	1
8	2-Jun-18	Sat			1	0	0	0	2	0	1
9	3-Jun-18	Sun
10	4-Jun-18	Mon
11	5-Jun-18	Tue
12	6-Jun-18	Wed
13	7-Jun-18	Thu
14	8-Jun-18	Fri
15	9-Jun-18	Sat
16	10-Jun-18	Sun
17	11-Jun-18	Mon
18	12-Jun-18	Tue
19	13-Jun-18	Wed
20	14-Jun-18	Thu
21	15-Jun-18	Fri	3
22	16-Jun-18	Sat
23	17-Jun-18	Sun	3
24	18-Jun-18	Mon
25	19-Jun-18	Tue
26	20-Jun-18	Wed
27	21-Jun-18	Thu
28	22-Jun-18	Fri
29	23-Jun-18	Sat
30	24-Jun-18	Sun
31	25-Jun-18	Mon	4
32	26-Jun-18	Tue
33	27-Jun-18	Wed
34	28-Jun-18	Thu
35	29-Jun-18	Fri
36	30-Jun-18	Sat
Sheet20

Cell Formulas
Range	Formula
AX7	=SUMPRODUCT(--(WEEKDAY($AU7:$AU36,2)=COLUMN(A1)*($AV7:$AV36<>"")))
AX8	=SUMPRODUCT(--(WEEKDAY($AU7:$AU36)=WEEKDAY(AX6)*($AV7:$AV36<>"")))

 

[ps50150]

Ahhh I see...Cell AX6 needs to be a normal date format as well...or the number 2 for Monday etc

Once I have changed that it seems to work! Thanks so much!

 

[jtakw]

You're welcome, welcome to the forum, glad you got it working.