COUNTIF(AND... Mulitple criteria

[mykal66]

Hi guys.

I'm stuck with a formula and looking for help if anyone can give me any pointers please?

I've used COUNTIF with several criteria in the past without any issues but this one is completely stumping me.

I have a spreadsheet which contain the following columns End Date & Sector: I need to count only items for a specific Sector that occurs for a specific sector e.g. Sales that end within a certain period e.g. March 2015

I have used a COUNTIF to count all items that occur in March 2015 (irrespective of the sector) as below which gives the right result.

=COUNTIF(Data!G6:G1000,">28/02/2015")-COUNTIF(Data!G6:G1000,">31/03/2015")

I'm stuck now trying to expand the formula to only return a count for items in s specific sector for that period. I've messed with sumproduct and countif(and but can't get anything to work.

I've attached an example as my description probably isn't the best. In the attached example F2 should show the count for sales in March 2016 which should be 5

As always thank for any help / pointers

Best wishes

Mykal

12924

[Aflatoon]

You can use COUNTIFS:

=COUNTIFS($A$2:$A$169,">="&DATE(2016,3,1),$A$2:$A$169,"<"&DATE(2016,4,1),$B$2:$B$169,"Sales")

[p45cal]

=COUNTIFS($A$2:$A$169,">=1/03/2016",$A$2:$A$169,"<=31/03/2016",$B$2:$B$169,"Sales")
or:
=SUMPRODUCT(($A$2:$A$169>=DATE(2016,3,1))*($A$2:$A$169<=DATE(2016,3,31))*($B$2:$B$169="Sales"))

Ignoring whether Sales:
=COUNTIFS($A$2:$A$169,">=1/03/2016",$A$2:$A$169,"<=31/03/2016")
or:
=SUMPRODUCT(($A$2:$A$169>=DATE(2016,3,1))*($A$2:$A$169<=DATE(2016,3,31)))

[mykal66]

Hi p45cal - I think it was you who helped me last time too? Thanks you so much, spent hours messing around trying to get it to work, I've never used COUNTIFS before!
Thank you so much. I really appreaciate the help you guys give and learn something new each time.
Much appreciated.
Mykal

[mykal66]

Hi Aflatoon - works perfect and even better way to use the dates. Thanks you so much. As i said below i really do appreaciet help from you guys, i try before i come on the boards as love to figure things out if possible but sometimes a brick wall is a brick wall. Thank you again