Count by month ONLY FILTERED columns and exclude blanks

[IsabellaLearning]

Hi if it was just counting july - i think it would be easy:
=SUMPRODUCT(--(MONTH(f:f)=7))

I would like to count only in the filtered data, only the July 2017s.
The date column in F row is in format dd/mm/yyyy I want to ignore blanks in that row.
I also would like to count by another condition eg:
If column e = shoes then count how many shoes (column e) are sales in July 17.

For my own learning I would like to know how to do it just for the date (showing filtered only) and secondly the filtered date with
the condition of shoes.

any help would be amazing
Izzy

 

[Nishant94]

Re: how to count by month ONLY FILTERED columns and exclude blanks

Unknown
	E	F	G	H
1		Date	Type	Sales (in Units)
2		04/09/2017	Dress	55
3		04/10/2017	Shoes	77
4		14/07/2017	Shoes	37
5		25/05/2017	Dress	27
6		28/04/2017	Dress	66
7		08/06/2017	Dress	19
8		06/08/2017	Dress	24
9		08/07/2017	Shoes	15
10		18/07/2017	Shoes	26
11		18/05/2017	Shoes	49
12		18/05/2017	Gloves	19
13		18/05/2017	Dress	4
14		30/07/2017	Dress	94
15		22/04/2017	Gloves	71
16		17/07/2017	Shoes	81
17		25/06/2017	Shoes	42
18		16/07/2017	Gloves	63
19		10/06/2017	Gloves	67
20		14/09/2017	Gloves	16
21		14/12/2017	Shoes	69
22		14/01/2018	Dress	18
23		09/12/2017	Gloves	3
24
25				942
26
27	Month	Year	Type	Count
28	July	2017	Shoes	4
29
Sheet17

Cell Formulas
Range	Formula
H28	{=SUMPRODUCT(--(IF(SUBTOTAL(103,OFFSET(H2,ROW($H$2:$H$23)-ROW($H$2),,,)),TEXT($F$2:$F$23,"mmmm")&YEAR($F$2:$F$23)&$G$2:$G$23)=E28&F28&G28))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

Let E5:F600 house the data, including the headers.

E1: shoes (an item/product of interest)

In F1 enter:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(F6,ROW(F6:F600)-ROW(F6),0,1)),--(E6:E600=E2))

Note that the autofilter is applied on column F.


If you are not using autofilter...

=SUMPRODUCT(--(F6:F600-DAY(F6:F600)+1=DATE(2017,7,1)),--(E6:E600=E2))

 

[IsabellaLearning]

Thankyou so much I should have said, auto filters are on both columns....


I actually became mesmerized and started to attempt ways to look through all the data without filtering: what do you make of this forumula I came up with to count only if H has the word Employee and then count everything EXCEPT the word NZ? It comes back with a zero which is not correct.


=SUMIFS('QREC New Test Report'!A:A,'QREC New Test Report'!H:H,"Employee",'QREC New Hire Report'!J:J,"<>*NZ*")


Izzy

Let E5:F600 house the data, including the headers.

E1: shoes (an item/product of interest)

In F1 enter:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(F6,ROW(F6:F600)-ROW(F6),0,1)),--(E6:E600=E2))

Note that the autofilter is applied on column F.


If you are not using autofilter...

=SUMPRODUCT(--(F6:F600-DAY(F6:F600)+1=DATE(2017,7,1)),--(E6:E600=E2))

 

[Aladin Akyurek]

Thankyou so much I should have said, auto filters are on both columns....

That shouldn't matter for the SumProduct formula with Offset...

I actually became mesmerized and started to attempt ways to look through all the data without filtering: what do you make of this forumula I came up with to count only if H has the word Employee and then count everything EXCEPT the word NZ? It comes back with a zero which is not correct.

=SUMIFS('QREC New Test Report'!A:A,'QREC New Test Report'!H:H,"Employee",'QREC New Hire Report'!J:J,"<>*NZ*")

Izzy

The SumIfs formula that you have appears to sum the A column while H equals employee and J on a different sheet does not contain NZ.

25,employee,life in NZ easy
40,employee,duncea
20,employee,#
60,administrator,gardea

# means an empty cell in the foregoing data set. Your SumIfs formula should yield here a total of 60 if both Test and Hire sheets exist and they contain appropriate data.