COUNT IF

[BeckaB]

I have a sheet with data in for months, i have done 1 count if statement to count the number of cars sold in 1 month based on the month being filtered on another cell.

What i want to do is add another part to the formula to say "Count if the number of cars sold in column X when the month is X but then another bit to say "IF "ALL" is selected return the count for the full year rather than just 1 months data

Is this possible?

 

[Eric W]

Maybe something like this?

Book1
	A	B	C	D	E
1	Month	Car		Month	Count
2	Jan	Honda		All	12
3	Feb	Ford
4	Feb	Acura
5	Jan	Lexus
6	Jan	Jaguar
7	Feb	Chevy
8	Feb	Expedition
9	Feb	Hyundai
10	Feb	Maserati
11	Jan	Mini Cooper
12	Jan	Toyota
13	Jan	Subaru
14
Sheet11

Cell Formulas
Range	Formula
E2	E2	=COUNTIF(A2:A13,IF(D2="All","*",D2))

 

[BeckaB]

Hi,

That doesn't work, I've done a count ifs:

=COUNTIFS('Commission Data - Bookings'!F:F,Build!B7,'Commission Data - Bookings'!P:P,Build!C1,'Commission Data - Bookings'!Q:Q,Build!C2,'Commission Data - Bookings'!R:R,Build!C3)

The month on the data tab is column Q and the month dropdown where I have Jan-Dec and then All is cell C2 on the build tab

 

[BeckaB]

Have written both formulas so the code works, just need to combine into one


=IF($C$2="All",COUNTIFS('Commission Data - Bookings'!F:F,Build!B7,'Commission Data - Bookings'!P:P,Build!$C$1,'Commission Data - Bookings'!R:R,Build!$C$3),COUNTIFS('Commission Data - Bookings'!F:F,Build!B7,'Commission Data - Bookings'!P:P,Build!$C$1,'Commission Data - Bookings'!Q:Q,Build!$C$2,'Commission Data - Bookings'!R:R,Build!$C$3))

=IF($C$1="All",COUNTIFS('Commission Data - Bookings'!F:F,Build!B7, 'Commission Data - Bookings'!Q:Q,Build!$C$2,'Commission Data - Bookings'!R:R,Build!$C$3),COUNTIFS('Commission Data - Bookings'!F:F,Build!B7,'Commission Data - Bookings'!P:P,Build!$C$1,'Commission Data - Bookings'!Q:Q,Build!$C$2,'Commission Data - Bookings'!R:R,Build!$C$3))


how do I combine?

 

[Eric W]

You can use the technique I showed in post 2 to shorten both of those formulas like this:

=COUNTIFS('Commission Data - Bookings'!F:F,Build!B7,'Commission Data - Bookings'!P:P,Build!$C$1,'Commission Data - Bookings'!Q:Q,IF($C2="All","*",Build!$C$2),'Commission Data - Bookings'!R:R,Build!$C$3)

=COUNTIFS('Commission Data - Bookings'!F:F,Build!B7,'Commission Data - Bookings'!P:P,IF($C$1="All","*",Build!$C$1),'Commission Data - Bookings'!Q:Q,Build!$C$2,'Commission Data - Bookings'!R:R,Build!$C$3)

And when you say you want to combine them, I'm not entirely clear what you want, but using that same technique, I think it would be something like this:

=COUNTIFS('Commission Data - Bookings'!F:F,Build!B7,'Commission Data - Bookings'!P:P,IF($C$1="All","*",Build!$C$1),'Commission Data - Bookings'!Q:Q,IF($C2="All","*",Build!$C$2),'Commission Data - Bookings'!R:R,Build!$C$3)