COUNTIFS Function - HELP

[RandomPezzer]

Hi Guys,

I'm trying to use the countif function to display the number of cells with certain text in them.

for example:

Column A will have either Car, Bike or Van in the cells and column B will have a date.

But I want to work out how many Car and Bike are on date 01/01/2015

This works:

Please Login or Register  to view this content.

but is there an easier way for example (I have tried it but doesn't work):

Please Login or Register  to view this content.

I don't have to use the countif function if there is a better function/formula to use

Thanks for any help!

[InvisibleMan]

Try

Please Login or Register  to view this content.

[Tony Valko]

=SUM(COUNTIFS(A:A,{"Car","Bike"},B:B,01/01/2015))

You need to quote the date:

=SUM(COUNTIFS(A:A,{"Car","Bike"},B:B,"01/01/2015"))

[InvisibleMan]

Thanks Tony ... just focused on the SUM. Didn't notice the subtle difference in the formulae.

[Tony Valko]

Or, use cells to hold the criteria:

D1 = car
D2 = bike
E1 = 1/1/2015

=SUMPRODUCT(COUNTIFS(A:A,D1:D2,B:B,E1))

[InvisibleMan]

Am I right in thinking that SUMPRODUCT isn't good with full column references?

[Tony Valko]

Correct.

However, in this application it's the COUNTIFS function that is evaluating the entire columns and that function does it efficiently.

[InvisibleMan]

Fair enough. Thought that might be the case but just wanted to check.

[RandomPezzer]

Thank you very much for your help guys this worked a treat !

Please Login or Register  to view this content.

[RandomPezzer]

One more question, if you don't mind pointing me in the right direction.

If say I had "C1" value = "Feb"

and a button which opened the xlsx file.

how would i reference the

Please Login or Register  to view this content.

to the Feb.xlsx file, but cell "C1" value wont always equal "Feb" it can equal any other month for example "Jan" or "Mar"

(the button VBA coding is already sorted its just the COUNTIF formula)

[Tony Valko]

Like this...

=SUM(COUNTIFS(INDIRECT("'["&C1&".xlsx]Sheet1'!A:A"),{"Car","Bike"},INDIRECT("'["&C1&".xlsx]Sheet1'!B:B"),"01/01/2015"))

Use the appropriate sheet name.

Note that the file Feb.xlsx (or whatever the file name) must be open for that formula to work.

[RandomPezzer]

Thanks Tony

[Tony Valko]

You're welcome!