Need a code/formula if some conditions meet

[Vishaal]

HI All,

Thanks in advance,

we have the following data

Time	name	Option
5-3-2020 16:28:28	name1	Soap
5-3-2020 16:30:07	name1	Soap
5-3-2020 16:39:12	name1	Soap
5-4-2020 5:31:19	name4	Juice
5-5-2020 5:44:38	name2	Soap
5-5-2020 5:44:57	name2	Juice
5-5-2020 5:45:35	name2	suggesion
5-5-2020 5:46:39	name2	Mango
5-5-2020 5:47:34	name2	idea

want some result from the above sheet to below sheet

Sales Reporting Sheet.xlsx
	A	B	C	D
1	Name	Data
2		Amount	Soap	Juice
3	name1		#VALUE!
4	name2		0
5	name3		0
6	name4		0
Sheet3

Cell Formulas
Range	Formula
C3	C3	=COUNTIFS(Sales!B$2:B43,A3,Sales!C$2:C1109,C2)
C4:C6	C4	=COUNTIFS(Sales!B3:B1110,A4,Sales!C3:C1110,C3)

i want how many times the said condition matched and result will show in sheet3!c4 - condition one, if sheet3!a4 match Sales!B2:B and condition two is sheet3!c3 match Sales!C2:C

i have tried a formula in sheet3!c4 but its not working

help pls

 

[DanteAmor]

How about

The size in the ranges must be the same :

$B$2:$B$43 and $C$2:$C$43

varios 09jun2020.xlsm
	A	B	C	D
1	Name	Data
2		Amount	Soap	Juice
3	name1		3	0
4	name2		1	1
5	name3		0	0
6	name4		0	1
Sheet3

Cell Formulas
Range	Formula
C3:D6	C3	=COUNTIFS(Sales!$B$2:$B$43,$A3,Sales!$C$2:$C$43,C$2)

 

[Vishaal]

thans DanteAmor ji,

can we use complete column, i mean in your formula can we select range Sales!B2:B and Sales!C2:C

 

[DanteAmor]

Of course

=CONTAR.SI.CONJUNTO(Sales!$B:$B,$A3,Sales!$C:$C,C$2)

 

[Vishaal]

its showing
#NAME?

 

[DanteAmor]

Sorry, I forgot to translate.

Use this please:

=COUNTIFS(Sales!$B:$B,$A3,Sales!$C:$C,C$2)

 

[Vishaal]

its perfect working,

Many thanks to your and your formula

may i know why in #2 formula is giving the wrong result i mean with the current formula we are getting the value 9, its right
and from previous formula we are getting 8

 

[DanteAmor]

Maybe the data range.

 

[Vishaal]

Thanks sir

 

[DanteAmor]

Youre welcome.