Dates and Countifs

jwgreen1986

Board Regular
Joined
Mar 8, 2019
Messages
64
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")))
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,110
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 8, 2019
Messages
64
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top