Count Rows with Criteria in Multiple Columns using namedranges

[dineshchaturvedi]

I am trying to come up with a formula which will give me a count of all rows that meet my criteria. I have two columns where I am trying find count which meets both the criteria. Example Column F Should have one of the values in named range CoreDef and Column J should have status values in named range DevTestRemaining.

I am not hung up on using one formula. I tried many different ways, used countifs then found it does not work well. Tried wrapping countif with Sumproduct and even used Sumproduct. You can see my struggle in attached file. I was playing with formulas in Var sheet. generally I get something when I google but this time not so lucky.

 

[Awesome Wally]

Try:
=SUMPRODUCT(--('UAT schedule'!$J$3:$J$306=Var!$H$5)+--('UAT schedule'!$J$3:$J$306=Var!$H$4),--('UAT schedule'!$F$3:$F$306=$B$6))

See if you get the desired result. I come up with 11 values that have In Dev & Core Defect and/or In Test & Core Defect.

 

[dineshchaturvedi]

Try:
=SUMPRODUCT(--('UAT schedule'!$J$3:$J$306=Var!$H$5)+--('UAT schedule'!$J$3:$J$306=Var!$H$4),--('UAT schedule'!$F$3:$F$306=$B$6))

See if you get the desired result. I come up with 11 values that have In Dev & Core Defect and/or In Test & Core Defect.

Thanks what does -- do? Can you also please explain this to me, I will appreciate a lot.

 

[dineshchaturvedi]

I do not know what -- does but I think I understand what you did. You have included two conditions on one column and one on another. However I need something where the list can change. Basically people keep adding new values and that is why I was thinking named ranges.

 

[Awesome Wally]

The -- within the sumproduct changes the output calculation from TRUE & FALSE to 1 & 0. Check out this link for more info: http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

It converts the arrays' TRUE/FALSE arguments into boolean operators, i.e. 1/0.

Lincoln's explanation is spot on:
Ones and Zeroes are much easier for programming purposes. If you click on the tick next to your formula in Excel, and you've got the same query/expression twice (once with and once without the double hyphen), you'll see if present something like this:
Without "--" - "{FALSE,FALSE,TRUE,FALSE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE}"
With "--" - "{0,0,1,0,1,1,1,0,0,0,0,1}"

It's a very handy action to use when counting cells. In SUMPRODUCT, it's a fantastic substitute for running COUNTA alternatives, when running across multiple spreadsheets.

 

[bosco_yip]

Maybe…………

In E4, formula copy down :

=SUM(COUNTIFS('UAT schedule'!J:J,{"In Dev","In Test","Pending"},'UAT schedule'!F:F,B4))

Regards

 

[Awesome Wally]

dineshchaturvedi, You can also use: =SUMPRODUCT(--('UAT schedule'!$J$3:$J$306={"In Dev","In Test"})*--('UAT schedule'!$F$3:$F$306=$B$6)) but I was guessing you preferred cell references.

Unfortunately you can't use a Named Range in place of {"In Dev","In Test"}