Dates and Countifs

[jwgreen1986]

Hi, i am trying to produce a spreadsheet that allows me to view how many items someone has sold per month. i am using the below formula but it doesnt seem to be working. if i take the date ranges out then if works but it gives the overall sales the person has had. i basically just want to add the date ranges so i can then show how many they have sold in january.

Sales!H:H is the range in which the sales date information is kept.

=IF(B4="","",IF(OR(B4="New",B4="Combined"),COUNTIFS(Sales!Q:Q,"YES",Sales!S:S,A4,Sales!R:R,"New",Sales!H:H,">=01/01/2019",Sales!H:H,"<=31/01/2019")))

 

[steve the fish]

Does it produce 0 as a result? If so then your dates are probably not really dates but text. You can either convert them to true dates or use a sumproduct instead. You can find out by using, for example:

=ISNUMBER(H2)

where H2 contains one of your dates.

 

[jwgreen1986]

yea it is producing 0 as the result. is there any other way around this as the range it is looking in is fed from a different source and continuously updated. it will be a bit tedious if i am to update this every time it updates.

 

[jwgreen1986]

tried the is number and it is bringing back false. what is the sum product?

 

[steve the fish]

Paste here a sample of the dates