COUNTIFS Function using Named Ranges

[Gesyca_is_joy]

I am trying to create a COUNTIFS Function in Excel 2007.
Essentially what i have is a named range, for the example we will say i named it "NATO". the function I am trying to write is
a count is does not equal "NATO" and equals yes. Here is a copy of the live formula (referencing tables with values in it, but hopefully you get the idea.

=COUNTIFS(I_RMCM_OPEN[Sub Vendor],"<>"&SubNames,I_RMCM_OPEN[SLA],"Overdue")

when i try it in my live document i get no returns, no errors but no returns. I am wondering what i did wrong. is the formula not evaluating the 4 text stings in the named range individually?

In this example, the first four values are the values of the named range "NATO" therefore if i were fun this function i should get a count of 1 because "tango" is not one of the values in the named range and it also equals "yes"

alpha	yes
bravo	no
Charlie	yes
Delta	no
tango	yes

<TBODY>
</TBODY>

 

[Marcelo Branco]

Maybe something like this

	A​	B​	C​	D​
1​	Values​	Yes/No​		Count​
2​	alpha​	yes​		1​
3​	bravo​	no​
4​	Charlie​	yes​
5​	Delta​	no​
6​
7​	tango​	yes​

<TBODY>
</TBODY>

Formula in D2
=SUMPRODUCT(--ISNA(MATCH($A$2:$A$7,NATO,0)),--(B2:B7="Yes"))

Hope this helps

M.

 

[Gesyca_is_joy]

the values are on a separate table from the fourmula, although the range is on the same tab, they were only in a singular table in the example so that the example made a little more sense.

 

[Marcelo Branco]

the values are on a separate table from the fourmula, although the range is on the same tab, they were only in a singular table in the example so that the example made a little more sense.

The post above is just an example. Try to adapt it to your real case.

Or, please, post a real data sample, say 10 rows, and expected result.

M.

 

[Gesyca_is_joy]

ok so i don't know how to send an image for you to look at. but on one tab i have a table array named "MONTH" within that table is 8 columns, for the purposes of this exampl only the first and last matter.

the first is a list of su vendor names and the last is a formula that returns a value of "In SLA", "Overdue" or "No SLA". the first column could be blank but the last column will always have a value.

On a separate tab I have a table where I want to see the sum total of how many Overdue entries each sub vendor has. I have 4 big subvendors then a 5 which will be everyone else lumped together. that 5th one is where i need the formula we are talking about. I want a count of how many rows have a value in the sub vendor column that does not equal one of my four names and also has the value "overdue" in the last column, does that make a little more sense?

Sub Vendor	Info	Info	Info	Info	Date1	Date2	SLA Formula
ABC							In SLA
DEF							Overdue
GHI							No SLA
							In SLA
123							Overdue
ABC							Overdue
DEF							Overdue

<TBODY>
</TBODY>

 

[Marcelo Branco]

Say you have a named range SubVendor that contains
ABC
DEF
GHI
KLM

and your table MONTH, in another sheet, is like this

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Sub Vendor​	Info1​	Info2​	Info3​	Info4​	Date1​	Date2​	SLA Formula​
2​	ABC​							In SLA​
3​	DEF​							Overdue​
4​	GHI​							No SLA​
5​								In SLA​
6​	123​							Overdue​
7​	ABC​							Overdue​
8​	DEF​							Overdue​
9​	345​							Overdue​
10​	678​							Overdue​

<TBODY>
</TBODY>

Try this formula

=SUMPRODUCT(--ISNA(MATCH(MONTH[Sub Vendor],SubVendor,0)),--(MONTH[SLA Formula]="Overdue"))

that results in 3 considering the table above.

Hope this helps

M.

 

[Gesyca_is_joy]

Perfect Thanks!!

 

[Marcelo Branco]

Perfect Thanks!!

You are welcome. Glad for helping.

M.

 

[Gesyca_is_joy]

Ok, i just ran it live and it is pulling fields that don't have a value in the Subvendor field, how do i fix that? I need the formula to only look at rows where the sub vendor has a value but doesn't equal the names in my named range. I can add a column as well to pull items that are overdue with no value in the subvendor field, but that function is simple and I am pretty sure I can figure it out on my own.

 

[Marcelo Branco]

Ok, i just ran it live and it is pulling fields that don't have a value in the Subvendor field, how do i fix that? I need the formula to only look at rows where the sub vendor has a value but doesn't equal the names in my named range. I can add a column as well to pull items that are overdue with no value in the subvendor field, but that function is simple and I am pretty sure I can figure it out on my own.

Maybe...

=SUMPRODUCT(--ISNA(MATCH(MONTH[Sub Vendor],SubVendor,0)),--(MONTH[SLA Formula]="Overdue"), --(MONTH[Sub Vendor]<>""))

M.