if value matches value in different sheet then countif
-
-
-
All criteria ranges need to be the same size.
What you can do is add a helper column, at M4 enter formula: =COUNTIF(I4:L4,"No") copied down.
then you can adjust your formula to:
=COUNTIFS(Sheet3!$C$4:$C$1048576,Sheet3!$C$4,Sheet3!$E$4:$E$1048576,Sheet3!E4,Sheet3!$M$4:$M$1048576,">0")
-
All criteria ranges need to be the same size.
What you can do is add a helper column, at M4 enter formula: =COUNTIF(I4:L4,"No") copied down.
then you can adjust your formula to:
=COUNTIFS(Sheet3!$C$4:$C$1048576,Sheet3!$C$4,Sheet3!$E$4:$E$1048576,Sheet3!E4,Sheet3!$M$4:$M$1048576,">0")
Sorry but i'm not quite getting it.
please correct me if i'm wrong. So I wlil be putting the =COUNTIF(I4:L4,"No") to what sheet? sheet4? and then the =countif(....) in sheet 4 as well? Am I right? sorry got a lil bit confused. -
All criteria ranges need to be the same size.
What you can do is add a helper column, at M4 enter formula: =COUNTIF(I4:L4,"No") copied down.
then you can adjust your formula to:
=COUNTIFS(Sheet3!$C$4:$C$1048576,Sheet3!$C$4,Sheet3!$E$4:$E$1048576,Sheet3!E4,Sheet3!$M$4:$M$1048576,">0")
Do you mind if I attached my sample workbook here?
-
The =COUNTIF(I4:L4,"No") would start in M4 of Sheet3.
The COUNTIFS formula would then go where you want your result to show ( I guess sheet 4).
Yes, if you would like, post your workbook here.
-
-
Here it is sir. there is a value in M4. but you can check the file here
-
For your purposes perhaps it might be better to use SUMPRODUCT
E.g. in C3 of Sheet4 you can enter formula:
=SUMPRODUCT((Sheet3!$E$4:$E$20=A3)*(Sheet3!$C$4:$C$20=B3)*(Sheet3!$I$4:$L$20="No"))
you would need to adjust the ranges to suit your database in Sheet3, but try not to use whole column or very large ranges as Sumproduct will slow down calculations.
Also make sure that the Agent Name structure is the same in both sheets for matches to occur.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!