Count rows for complete columns

[Anonymous]

Hello, 

I have an issue. I try to count rows but I don't know how. 

I need to take into account too following stuffs: 

Each row has to filled 3 columns: 

• asset name 
• asset status 
• asset class (yellow columns)

or this 3 columns: 

• name 
• fqdn 
• IP (green columns)

I can only count values if I have complete values at first in columns: asset name, asset status, asset class and if I don't have values in this 3 columns I take into account this 3 columns: name, fqdn, IP. 

For above example result should = 19 rows because at first I count values if 3 columns (asset name, asset status, asset class) are complete and if I have blank values for this 3 columns  (asset name, asset status, asset class) I should count values for 3 complete columns:  name, fqdn, IP and then all add. 

I have no idea how to do this, can you help me? 

[rsbin]

@Anonymous ,

I think I have a solution for you.  But please post the above data as a table not as an image.

Then I can work with it more easily and give you some suggestions.

Regards,

[Anonymous]

@rsbin of course, thanks a lot for your help! 

Product	Asset Name	Asset Status	Asset Class	Name	FQDN	IP
P1	A1	Ready	C1
P1	A2	Ready	C1
P1	A3	Ready	C1
P2	A4	Ready	C1
P2	A5	Ready	C1
P2	A6	Operational	C2
P3	A7	Operational	C3
P4	A8	Ready	C4
P4	A9	Ready	C4
P4	A10	Ready	C4
P4	A11	Ready	C4
P5	A12	Ready	C5
P5	A13	Ready	C5	A13	T1	1
P5	A14	Ready	C5	A14	T2	2
P5	A15	Ready	C5	A15	T3	3
P6				A15	T3	4
P6				A17	T3	5
P7				A18	T4	6
P7				A19	T4	7
P7

[rsbin · Accepted solution]

@Anonymous ,

Have attached a pbix file for you to refer.

My approach was to create several columns to test  your Yellow and Green columns.

Then finally add up the columns that match your conditions. 

Made use of the AND function.  But this only accepts two arguments, so had to "nest" them to account for each set of 3 columns.

Hopefully will be clear when you review the file.

Any questions, please ask, but I will be signing off shortly.  Can revisit tomorrow if need be.

Good Luck and Regards,