Excel conditional countif 2

[SjrH]

I'm trying to formulate a countif total of cells that meet a given criteria, but only whilst another range of cells also meet another criteria.

Is this even possible with Excel?!

Basically, I have a very long list of computers which, amongst other information, show me the subnet on which they reside and a PASS / FAIL value based on a vbscript calculation done when the data was collated.

What i'm trying to achieve is to calculate the number of systems per subnet which i've got working with

=countif(ipscope,"xx.xx.xx.*") {where ipscope is a defined name for the column containing the ip address}

Now I would like to be able to tell how many on that subnet have a status of PASS.

Looking around, I see that countif is not suitable for multiple conditions and that sumproduct might work. However, all I can get from this is #NUM!

Thanks



[small]Listen to those who know, believe in those that do[/small]

 

[AnotherHiggins]

Try this:
[COLOR=blue white]=SUMPRODUCT((B2:B8="pass")*(LEFT(A2:A8,9)="xx.xx.xx."))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[KenWright]

Though if you still get #NUM you may want to try this version of the formula:-

=SUMPRODUCT(--(B2:B8="pass"),--(LEFT(A2:A8,9)="xx.xx.xx."))

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[khobson]

Another method is to use an array.
e.g. {=SUM((LEFT(A2:A9,9)="10.36.12.")*(B2:B9="Pass"))}

Braces are not typed. They are entered after you press Ctrl+Shift+Enter while editing the formula.

It is best not to use range names in some of these formulas. Also note that the text comparison is not case sensitive.

 

[xlbo]

Arrays do exactly the same as SUMPRODUCT but with the disadvantage that you have to remember to enter them with ctrl+shift+enter......

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SjrH]

Thanks

I'll give them ago tomorrow, i've lost enough hair today!

[small]Listen to those who know, believe in those that do[/small]

 

[khobson]

Most use the SumProduct(). Some recommend multiplying by 1 in that formula. I have not seen a case where it made a difference though.

In a third method that is more complicate:
{=SUM((IF(LEFT(A2:A9,9)="10.36.12.", IF(B2:B9="Pass",1,0),0)))}

Range names should be ok.

In a 4th method:
You might find functions like DCount() and DCountA() handy. While you need to set up a Criterion table to use them, they can be quite handy.
e.g.

a1:
ipscope       ipstate
10.36.12        PASS
10.36.12.132    PASS
192.149.244.45  PASS
12.12.14.5      FALSE
10.36.12.13     FALSE
10.36.12.132    FALSE
192.149.244.45  FALSE

c1:
=DCOUNTA(A1:B9, "ipscope", L1:M2)

Criterion table, 
L1:
ipscope         ipState
10.36.12.       PASS

 

[AnotherHiggins]

Another method is to use an array.

As Geoff pointed out, the SumProduct function is an array function in how it is calculated.

Most use the SumProduct(). Some recommend multiplying by 1 in that formula. I have not seen a case where it made a difference though.

I'm sure one of the gurus will correct me if I'm wrong, but the way I understand it is that using a double negative (--) is a way to coerce the data to return a boolean (True/False). If SjrH is getting an error when trying to use the SumProduct funtion, then adding in the "--" will likely help (assuming everything is typed correctly).

One could also multiply by one or add zero to force the formula to return a boolean.

Examples:
=SUMPRODUCT(0+(B2:B8="pass"),0+(LEFT(A2:A8,9)="xx.xx.xx."))
=SUMPRODUCT(1*(B2:B8="pass"),1*(LEFT(A2:A8,9)="xx.xx.xx."))

But I think pointing all of this out is more likely to confuse SjrH than help him/her.

SjrH: give Ken's formula:
=SUMPRODUCT(--(B2:B8="pass"),--(LEFT(A2:A8,9)="xx.xx.xx."))
a shot.

Are you still having problems?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[KenWright]

Most use the SumProduct(). Some recommend multiplying by 1 in that formula. I have not seen a case where it made a difference though.

If you have time for a read, then give the following a go, and you'll see why it becomes a necessity in some cases:-

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Also, range names 'are' fine but you need to be very sure that the dimensions of each of the ranges are the same else it will error out on you.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SjrH]

Thanks Ken, that hit the spot!

I couldnt make it work with the named range definitions, but it worked a treat when the data was pasted into the same workbook and refrenced from there.



[small]Listen to those who know, believe in those that do[/small]

 

[KenWright]

Ahhhhh - I don't recall the name referencing another workbook.

Had you inserted the named ranges for your source data into the workbopok where your formula was, eg if your data was in say 'testlink.xls' and your formula was in 'summary.xls' then in the workbook 'summary.xls' you need to insert a named range that has a full file link back to the source data, eg Insert / name / Define, call it aaaa and then have it refer to ='D:\[TestLink.xls]Sheet1'!$B$4:$B$30.

Then you can just use something like

=SUMPRODUCT(--(aaaa=F2),bbbb)

where aaaa is a named range that refers to
='D:\[TestLink.xls]Sheet1'!$B$4:$B$30

and bbbb refers to
='D:\[TestLink.xls]Sheet1'!$C$4:$C$30

Only potential issue I can think of is if you try to make the range dynamic and use a function in there that won't work with a closed workbook.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------