Countifs where using 2 criteria on different column Microsoft Excel

Question

I've a table on excel as bellow :

The problem I am facing now is that I want to calculate the amount of data with the following conditions:

Scene 1: 1. Mikaela purchases DEV01 (can be DEV02 or DEV03) which has the MDN serial number: 88217094212 2. If Mikaela performs a top up PUL100 (can bePUL60 or PUL50) on the MDN serial number: 88217094212 3. Then the condition will be calculated 1

Scene: 1. John made a purchase of DEV01 (can be DEV02 or DEV03) which has the MDN serial number: 88900253676 2. If John doesn't top up PUL100 (can be PUL60 or PUL50) on the MDN serial number: 88900253676 3. Then the condition is not counted

In the table below the following data are eligible as above have been marked yellow block calculated as 1 count, if calculated as a whole then the number should appear = 3

I've tried by doing the following calculations:

= COUNTIFS (B2: B10, "DEV01")

From the results of these calculations I am still confused how to reference data calculation by using several criteria on different columns. Thanks for your help and cooperation.

Answer 1

You can use another formula:

=SUM((--(A2:A9&LEFT(B2:B9,3)=A2:A9&"DEV")*(--(A3:A10&LEFT(B3:B10,3)=A3:A10&"PUL"))*(--(D3:D10<>""))))  

Array Formula press Ctrl+Shift+Enter
Choose A2:A9 the Name column less 1 row from down (each Dev is above Pul to be counted)
B2:B9 column of DEV and PUL to make for example "MikaelaDEV" using left(B2:B9,3)
if =A2:A9&"DEV" it will evaluate to 1
the same for "PUL"
Choose 1 row less from up A3:A10 till the last row
it will evaluate to 1 when =A3:A10&"PUL"
D3:D10 <>"" evaluate to 1 if not empty
The product 1*1*1=1
Sum will add the 1 when the three conditions evaluate to 1

With Countifs you have to write:

=COUNTIFS(A2:A9,A3:A10,B2:B9,"DEV*",B3:B10,"PUL*",D3:D10,"<>""")

And change the references to your Data
Always make a difference of one row but the count of rows have to be the same