using SUMPRODUCT with DATE

[trader1990]

I am building inventory report to show how inventory is doing
When I do counts for each day I enter manual data into the excel table i have build
like in first column I have Date of cycle count, next part#, Stock location, physical count, variance value $
I tally all the numbers on a separate table to be more readable
I am having trouble with SUM product function and date range
I want to count how many variances are in a month,
The tables I use are
DATE of cycle count and Variance
in the month of January I have entered 700 lines and each line has a variance of either $0 or $ range in plus (extra stock) and minus (missing stock)
I want to count the variances how many I have done in January , february, march
originally I use this function =SUMPRODUCT(--(LEN(G23:G10000)>0)) and it calculates perfectly the variances but now I have data entry for February, March, and April
and this function counts all how can I make this work for each separate month?
all The dates are entered in A23:A10000 and variances are in G23:G10000

 

[jtakw]

Hi,

If I understand correctly, the following will do what you want, might need some tweaking if I misunderstood.
Also, the formula you're using to Count Variances can be done simply by a COUNTA function, as shown in L23 and M23.

Excel 2010
	A	B	G	H	I	J	K	L	M
22	Date		Variance		Month	Variance Count		Your sumproduct formula	Count formula
23	2/2/2018		1		Feb-18	2		8	8
24	3/5/2018		2		Mar-18	1
25	2/5/2018		3		Apr-18	3
26	4/15/2018		4
27	4/7/2018
28	10/11/2018		6
29	4/18/2018		7
30	7/20/2018		8
31	4/1/2018		9
Sheet18

Cell Formulas
Range	Formula
L23	=SUMPRODUCT(--(LEN(G23:G10000)>0))
M23	=COUNTA(G23:G10000)
J23	=SUMPRODUCT((MONTH(A23:A10000)=MONTH(I23))*(G23:G10000<>""))

 

[Aladin Akyurek]

Avoid testing just month, unless the year must be explicitly ignored... So, for correctness:

=SUMPRODUCT(--($A$23:$A$31-DAY($A$23:$A$31)+1=E23),--ISNUMBER($C$23:$C$31))

where E23 is a first day date like 2018-02-01 (means the month/year of February 2018).

 

[trader1990]

OK thank you let me try

 

[trader1990]

It worked perfectly thank you

 

[jtakw]

You're welcome, welcome to the forum.