Counting a range of cells based upon dates and info

[petelomax]

Hi

I have a spreadsheet with information regarding quotes that we do, and these quotes are done by 4 different people

I want to be able to analyse who does the most quotes per month.

So for example in february and march, we have NB, JB, PL and AH quoting, i want to be able to count how many quotes are done by each one per month

hope this helps

thanks

[Manikandan Arumugam]

Please attach the sample file with before and after

[Sam Capricci]

you could try countifs (test)

I am getting a sucuri block when I try to post a formula. sorry.

[petelomax]

I think i've found it using countifs and a few ranges and criteria. thanks!

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[petelomax]

i've not figured out the final formula, if you see the attached spreadsheet i need a formula to adhere to all of these criteria

Quotes in January, done by NB and has a job number

i tried by my answer wasn't correct

[canapone]

Hi,

this is a kind of countifs for NB, January 2017


=SUMPRODUCT((C$2:C$102="NB")*(D$2:D$102<>"")*(MONTH(B$2:B$102)=1)*(YEAR(B$2:B$102)=2017))


If you'd need to count every January, delete the red segment of the formula.


Hope it helps

[petelomax]

Then whatabout february?

[JohnTopley]

Try

=COUNTIFS(B:B,">="&E2,B:B,"<="&EOMONTH(E2,0),C:C,"NB",D:D,"?*")

E2 =01/01/2017

[Sam Capricci]

this is the formula I was trying to post earlier...
=COUNTIFS(A1:A20,"NB",B1:B20,"> 42794",B1:B20,"< 42826") where 42795 is the numeric value for 3/1/2017 and 42826 for 4/1/2017.
I had to add spaces to get it past the firewall but it works either way, adjust for your fields.