Count if formula

[Pardeepd]

Hello,

I need a formula to show the total number items sold each month by department then also count the total items sold by employee in that department.

Please could you let me know what formula to use?

 

[RasGhul]

Hi,

sumproduct & sumif or countif will help can you post some sample data?

 

[Pardeepd]

I need a formula to show the total products sold per week, I then need a formula to count against each product the quantity sold.
This is the data I have.
[TABLE="width: 244"]
<tbody>[TR]
[TD]Week
[/TD]
[TD]Product
[/TD]
[TD]Total
[/TD]
[/TR]
[TR]
[TD]03/08/2018
[/TD]
[TD]Mobile phone
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]03/08/2018
[/TD]
[TD]Laptop
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]03/08/2018
[/TD]
[TD]TV
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]03/08/2018
[/TD]
[TD]DVD Player
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]03/08/2018
[/TD]
[TD]Laptop
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]10/08/2018
[/TD]
[TD]Laptop
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]10/08/2018
[/TD]
[TD]Mobile Phone
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]10/08/2018
[/TD]
[TD]TV
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]10/08/2018
[/TD]
[TD]Laptop
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]

I would like it to show as below:
[TABLE="width: 373"]
<tbody>[TR]
[TD]Items sold
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week
[/TD]
[TD]Mobile phone
[/TD]
[TD]Laptop
[/TD]
[TD]TV
[/TD]
[TD]DVD Player
[/TD]
[/TR]
[TR]
[TD]04/08/2018
[/TD]
[TD] 1
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]10/08/2018
[/TD]
[TD] 1
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Total items sold per week
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week
[/TD]
[TD]Mobile phone
[/TD]
[TD]Laptop
[/TD]
[TD]TV
[/TD]
[TD]DVD Player
[/TD]
[/TR]
[TR]
[TD]04/08/2018
[/TD]
[TD] 5
[/TD]
[TD]11
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]10/08/2018
[/TD]
[TD] 3
[/TD]
[TD]9
[/TD]
[TD]15
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

Hi Par,

Copy these into your tables;

​

Book1
	A	B	C	D	E
1	Week	Product	Total
2	03-08-18	Mobile phone	5
3	03-08-18	Laptop	4
4	03-08-18	TV	7
5	03-08-18	DVD Player	5
6	03-08-18	Laptop	7
7	10-08-18	Laptop	5
8	10-08-18	Mobile Phone	3
9	10-08-18	TV	15
10	10-08-18	Laptop	4
11
12	Items sold
13	Week	Mobile phone	Laptop	TV	DVD Player
14	03-08-18	1	2	1	1
15	10-08-18	1	2	1	0
16
17	Total items sold per week
18	Week	Mobile phone	Laptop	TV	DVD Player
19	04-08-18	5	11	7	5
20	10-08-18	3	9	15	0
Sheet1

Cell Formulas
Range	Formula
B14	=COUNTIFS($B$2:$B$10,B$13,$A$2:$A$10,">="&$A14-5,$A$2:$A$10,"<="&$A14)
B19	=SUMIFS($C$2:$C$10,$A$2:$A$10,">="&$A19-5,$A$2:$A$10,"<="&$A19,$B$2:$B$10,B$18)

 

[Pardeepd]

Sorry I may not have explained this properly.

What I need is a formula to count the items selected/bought in week ending 3/8/2018 etc. In this case, this would be Mobile phone 1, laptop – 2, tv – 1 and DVD player – 1 and the same for week ending 10/8/2018.

Once I have these results I then need to know the quantity of items sold against each product in w/e 3/8/2018 and 10/8/018. I.e.

3/8/2018 - Mobile Phone (5 sold), laptop (11 sold), tv (7 sold) and dvd player (5 sold)
10/8/2018 - Mobile Phone (3 sold), laptop (9 sold), tv (15 sold) and dvd player (0 sold)

Could you help me with the formula to show the above as I then need to create a graph (clustered/stacked)

 

[Fluff]

RasGhul's formulae work for me, in what way are they not working for you?