CountIFs and SUMIFs Multiple Criteria

[Scott1sh]

Hi All looking for some help on a project I have been tasked with

I have a sheet of data that includes phone numbers and tracks data/phone usage

I have so far set it that If columns J, K, L and M are < 1 then they are Unused, otherwise they are Used (Column V)

In Column W if the adjacent cell is Unused then it will add 1, If Used then will show 0


What I need help with if possible

In my sample sheet I have Example Numbers 1, 2, 3

Example 1 has 3 unused and 1 used, the sum is 0 for the last row so that should Active Phones Count (C4)

Example 2 is all Unused and the sum is 4, so that should add 1 to the Inactive Phones Count (C5)

Example 3 is all Used and the sum is 0, so that should add 1 to the Active Phones Count (C4)


I am stuck on how to do this for each unique phone number and associated rows, ignoring duplicates ( For example if I SUM Column V for 0 it would show 5 Active Phones (C4), when I would like it to be 2)

sample sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Phone Usage
2	Period: 2019
3
4	Phone Numbers	Active Phones	Inactive Phones
5	3	0	0
6
7
8
9
10
11	SBSCR_NO	SBSCR_NAME	INV_PERIOD	ALLOCATION_CODE_1	ALLOCATION_CODE_2	ALLOCATION_CODE_3	ALLOCATION_CODE_4	BAN	BEN	CALL_DURATION	CREDIT_CHGS	DATA_VOLUME	NO_OF_CALLS	OTHER_CHGS	OTHER_CREDITS	RECURRING_CHGS	RECUR_CREDITS	TOTAL_CHGS	USAGE_CHGS	USAGE_CREDITS	INV_DATE	Status	Count
12	7443344963	EXAMPLE 1	01-Jun-20 to 30-Jun-20	HSCP	COMMHANDC	COMMCARE	G1125	670186744	1	0	0	0	0	0	0	3.44	0	3.44	0	0	25/07/2020 00:00	Unused	1
13	7443344963	EXAMPLE 1	01-Mar-21 to 31-Mar-21	HSCP	COMMHANDC	COMMCARE	G1125	670186744	1	0	0	0	0	0	0	3.44	0	3.44	0	0	24/04/2021 00:00	Unused	2
14	7443344963	EXAMPLE 1	01-Apr-21 to 30-Apr-21	HSCP	COMMHANDC	COMMCARE	G1125	670186744	1	0	0	0	0	0	0	3.44	0	3.44	0	0	25/05/2021 00:00	Unused	3
15	7443344963	EXAMPLE 1	01-Apr-22 to 30-Apr-22	HSCP	COMMHANDC	COMMCARE	G1125	670186744	1	0	0	0	1	0	0	3	0	3	0	0	24/05/2022 00:00	Used	0
16
17	7443566008	EXAMPLE 2	01-Jun-20 to 30-Jun-20	HSCP	COMMHANDC	COMMCARE	G1125	670186744	1	0	0	0	0	0	0	3.44	0	3.44	0	0	25/07/2020 00:00	Unused	1
18	7443566008	EXAMPLE 2	01-Mar-21 to 31-Mar-21	HSCP	COMMHANDC	COMMCARE	G1125	670186744	1	0	0	0	0	0	0	3.44	0	3.44	0	0	24/04/2021 00:00	Unused	2
19	7443566008	EXAMPLE 2	01-Apr-21 to 30-Apr-21	HSCP	COMMHANDC	COMMCARE	G1125	670186744	1	0	0	0	0	0	0	3.44	0	3.44	0	0	25/05/2021 00:00	Unused	3
20	7443566008	EXAMPLE 2	01-May-21 to 31-May-21	HSCP	COMMHANDC	COMMCARE	G1125	670186744	1	0	0	0	0	0	0	3.44	0	3.44	0	0	26/06/2021 00:00	Unused	4
21
22	71158063999	EXAMPLE 3	01-Jun-20 to 30-Jun-20	HSCP	COMMHANDC	COMMCARE	G1125	670186744	1	1	0	0	0	0	0	3.44	0	3.44	0	0	25/07/2020 00:00	Used	0
23	71158063999	EXAMPLE 3	01-Mar-21 to 31-Mar-21	HSCP	COMMHANDC	COMMCARE	G1125	670186744	1	0	0	1	0	0	0	3.44	0	3.44	0	0	24/04/2021 00:00	Used	0
24	71158063999	EXAMPLE 3	01-Apr-21 to 30-Apr-21	HSCP	COMMHANDC	COMMCARE	G1125	670186744	1	0	0	0	1	0	0	3.44	0	3.44	0	0	25/05/2021 00:00	Used	0
25	71158063999	EXAMPLE 3	01-May-21 to 31-May-21	HSCP	COMMHANDC	COMMCARE	G1125	670186744	1	0	0	0	1	0	0	3.44	0	3.44	0	0	26/06/2021 00:00	Used	0
Example

Cell Formulas
Range	Formula
A5	A5	=SUMPRODUCT(((A12:A5483<>"")/COUNTIF(A12:A5483, A12:A5483 &"")))
V12,V22,V17	V12	=IF(AND(J:J<1,K:K<1,L:L<1,M:M<1),"Unused","Used")
W22:W25,W17:W20,W12:W14	W12	=IF(V12="Unused",SUM(W11,1),0)
V23:V25,V18:V20,V13:V15	V13	=IF(AND(J:J<1, K:K<1, L:L<1, M:M<1), "Unused", "Used")
W15	W15	=IF(V15="Unused",SUM(#REF!,1),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A22:V25	Expression	=$V22="Used"	text	NO
A22:V25	Expression	=$V22="Unused"	text	NO
A17:V21,A12:V15	Expression	=$V12="Used"	text	NO
A17:V21,A12:V15	Expression	=$V12="Unused"	text	NO

 

[RobP]

Hi, sorry its not quite the answer you want - as I note you are using Excel 2019 - and I couldn't find a way to do it with that version. Maybe others here can succeed, or you are able to upgrade ?

In newer versions, you have a few extra tools at your disposal to filter the data.

=LET(data,FILTER(B12:B25,W12:W25=0,""),subset,UNIQUE(data),result,COUNTA(subset),result)-1

This formula for B5 will get your "2" result.
essentially it filters your data and returns the names in Col B that correspond to having a zero in Col W. So "data" becomes a list of subscriber names : EXAMPLE 1, 0, 0, EXAMPLE 3, EXAMPLE 3,EXAMPLE 3, EXAMPLE 3. The zeros are gotten from the blank rows you have.

Then, "subset" looks for all the unique values in "data", thus "subset" stores the items : EXAMPLE 1, 0, EXAMPLE 3.

Then finally "result" counts the number of items in "subset" (so in this case, it would be 3). The -1 on the end is essentially to remove the zeros, so giving you result "2". If you didnt have any blank rows in your data, you wouldn't need the "-1" on the end.

cheers
Rob

 

[Scott1sh]

Hi, sorry its not quite the answer you want - as I note you are using Excel 2019 - and I couldn't find a way to do it with that version. Maybe others here can succeed, or you are able to upgrade ?

In newer versions, you have a few extra tools at your disposal to filter the data.

=LET(data,FILTER(B12:B25,W12:W25=0,""),subset,UNIQUE(data),result,COUNTA(subset),result)-1

This formula for B5 will get your "2" result.
essentially it filters your data and returns the names in Col B that correspond to having a zero in Col W. So "data" becomes a list of subscriber names : EXAMPLE 1, 0, 0, EXAMPLE 3, EXAMPLE 3,EXAMPLE 3, EXAMPLE 3. The zeros are gotten from the blank rows you have.

Then, "subset" looks for all the unique values in "data", thus "subset" stores the items : EXAMPLE 1, 0, EXAMPLE 3.

Then finally "result" counts the number of items in "subset" (so in this case, it would be 3). The -1 on the end is essentially to remove the zeros, so giving you result "2". If you didnt have any blank rows in your data, you wouldn't need the "-1" on the end.

cheers
Rob

I upgraded to 365 :D

Thank you very much for the help, it works perfect

One last question is it possible to modify the LET function to something similar for column W

Right now Column W uses

=IF(V13="Unused",SUM(W12,1),0)

Which does its job, however, its not unique to the subscriber name so for example counts are 1 and 3, when it should be 1 and 2

SBSCR_NO	SBSCR_NAME	INV_PERIOD	ALLOCATION_CODE_1	ALLOCATION_CODE_2	ALLOCATION_CODE_3	ALLOCATION_CODE_4	BAN	BEN	CALL_DURATION	CREDIT_CHGS	DATA_VOLUME	NO_OF_CALLS	OTHER_CHGS	OTHER_CREDITS	RECURRING_CHGS	RECUR_CREDITS	TOTAL_CHGS	USAGE_CHGS	USAGE_CREDITS	INV_DATE	STATUS	COUNT
7827355649	MISS ERIN GOLDIE	01-Sep-19 to 30-Sep-19	REGULATORY	PLANANDBC	BUILDSTAND	F6300	670186744	1	0	0	0	0	0	0	1.33	0	1.33	0	0	26/10/2019	Unused	1
7827355649	MISS ERIN GOLDIE	01-Oct-19 to 31-Oct-19	REGULATORY	PLANANDBC	BUILDSTAND	F6300	670186744	1	0	0	0	0	0	0	1.33	0	1.33	0	0	25/11/2019	Unused	3

 

[RobP]

Hi, thanks for your feedback. How about if I come at it from another direction ?

If I modify my original formula for B5, to operate on column V, instead of Col W - it looks like this :

=LET(data,FILTER(B12:B25,V12:V25="Used",""),subset,UNIQUE(data),result,COUNTA(subset),result)

This has the benefit of not caring if you have blank rows or not in your data, searching only for "used" rows (and therefore we don't need the "-1" on the end).

I couldn't see any other references to Col W in your formulas, so figured you could remove it totally (as I also wasn't sure what you were trying to count there either). Then, if you have the number of phone numbers in A5, the number of Used numbers in B5, of course C5 would just need A5-B5 to get your result if I understood correctly ?

Let me know if thats what you needed, or I've misunderstood.
cheers
Rob

 

[Scott1sh]

Thanks again for the reply and help


I think I have probably explained it wrong initially on the intentions of the sheet


We get a set of data of mobile phone usage stats, and I look at data/call usage in Columns J,K,N,M

If a user has 3 months of no data/calls usage we need it to show so we can disable that phone (cost saving)


Primary Function

Mr Example Subscriber

If Columns J,K,N,M have all 0 (no data/call usage) then that adds 1 to Column W (1 month not used) - Needs specific to each unique subscriber

If Columns J,K,N,M have all 0 (no data/call usage) then that adds another 1 to Column W making it 2 (2 months not used) - Again needs to count for that unique subscriber

If Columns J,K,N,M have anything above 0 (no data/call usage) then that adds 0 to Column W

Column V is really for conditional formatting and highlight used/unused data


Secondary Function

Count active/inactive phones based on unique subscriber (Which you have already helped with)


Hope this makes sense, if not ill try and explain it better :D

 

[RobP]

Hi again,

can I ask - is your data (ie. the way you receive it) in the same format as above (eg. with each subscriber grouped together nicely, and having a space between each subscriber ?), or do you have just a list of all subscribers, that could be anywhere in the list ? . Also - it looks to me like you are not really caring about having 3 continuous months before disabling the phone (if I look at col C), is that correct ? So any month of non-usage contributes to an additional count of 1 in Col W, yes ?

So in your example 1, you would get to your count of 3 on row 14, and then disable the phone, correct ?

thanks
Rob

 

[Scott1sh]

Hi again,

can I ask - is your data (ie. the way you receive it) in the same format as above (eg. with each subscriber grouped together nicely, and having a space between each subscriber ?), or do you have just a list of all subscribers, that could be anywhere in the list ? . Also - it looks to me like you are not really caring about having 3 continuous months before disabling the phone (if I look at col C), is that correct ? So any month of non-usage contributes to an additional count of 1 in Col W, yes ?

So in your example 1, you would get to your count of 3 on row 14, and then disable the phone, correct ?

thanks
Rob

No the data we receive is a huge list, thousands of rows

The list is all subscribers in a defined period and can be anywhere in the list (usage is monthly)


Correct for Example 1 - Once count is 3 (3 months non usage) we would disable the phone

I can upload the sheet if it makes it easier

 

[RobP]

Hi, so would something like this work for you in Col W.

=LET(names,FILTER(B$12:B$25,(B$12:B$25=B12)*(V$12:V$25="Unused")),num,COUNTA(names),IF(num>=3,num,0))

Rather than try to insert a numerical sequence (that I dont see a use for in what you are saying you want to do), it will just count how many unused by Subscriber name, and if its 3 or more, it will put the total in Col W. Otherwise, a zero ? (in the last part of the IF statement at the end,

(num>=3,num,0))

, if you replace the second num with a 3, it will just put 3 in each cell if its 3 or above - which might make it easier to filter ?

Let me know if it works for you ?

Rob

 

[Scott1sh]

@RobP

That works, I can modify the conditional formatting to suit

Its is as close as we will get it to what I needed

Thanks again for the help

 

[RobP]

great - thanks for the feedback.

Rob