COUNT cells with two conditions 1

[pmcmicha]

I know that I cannot use COUNTIF with more than one condition and I have tried to use the 'CONDITIONAL SUM' wizard as well (This failed since my cells are merged.). But I have two conditions which need to be met.

COUNTIF(A1:A100,"*text")

This is what I am trying to do, but I need to limit my range based on a variable which changes depending on what level you are currently working with. So if the CUR_LVL would be 5, then the range would only need to be A1:A5. But later on, the CUR_LVL could be 1 or 10 or 100.

Is there any way to specify a dynamic range or is there another way to get the solution I am looking for, but one which I cannot see?

Thanks in advance.

 

[KenWright]

Sorry, but those merged cells are going to screw you up every time. Lose them if you want to ensure no problems with data ranges in these kind of formulas. Consider "Centre across selection" instead of "Merge Cells"

Dynamic ranges can then be reliably created with functions such as OFFSET.

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

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

----------------------------------------------------------------------------

 

[pmcmicha]

I found the solution to my problem a little while ago and wanted to post a follow up. Here is what I did:

DNAME# == Defined Name
DNAME1=S4!$AJ$36:$AJ$43
DNAME2=INDIRECT(CONCATENATE("S3!$H$48:$H$",48+(S1!$BN$2)*2))
DNAME3=INDIRECT(CONCATENATE("S3!$U$48:$U$",48+(S1!$BN$2)*2))

Array Forumla:
{=SUM(IF(COUNTIF(DNAME1,TRANSPOSE(DNAME2))=1,1,0))+SUM(IF(COUNTIF(DNAME1,TRANSPOSE(DNAME3))=1,1,0))}

My solution ended up being slightly more complex than I originally intended, but then the problem was more complex than I first thought. At any rate, I hope this will help someone else and thanks to everyone on this forum for your assistance.