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"
[TABLE="width: 500"]
<TBODY>[TR]
[TD]alpha[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]bravo[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]Charlie[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]Delta[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tango[/TD]
[TD]yes[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Marcelo Branco]

Maybe something like this


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Values​

[/TD]
[TD]

Yes/No​

[/TD]
[TD][/TD]
[TD]

Count​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

alpha​

[/TD]
[TD]

yes​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

bravo​

[/TD]
[TD]

no​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Charlie​

[/TD]
[TD]

yes​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

Delta​

[/TD]
[TD]

no​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

tango​

[/TD]
[TD]

yes​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


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?

[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]Sub Vendor[/TD]
[TD]Info[/TD]
[TD]Info[/TD]
[TD]Info[/TD]
[TD]Info[/TD]
[TD]Date1[/TD]
[TD]Date2[/TD]
[TD]SLA Formula[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]In SLA[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Overdue[/TD]
[/TR]
[TR]
[TD]GHI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]No SLA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]In SLA[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Overdue[/TD]
[/TR]
[TR]
[TD]ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Overdue[/TD]
[/TR]
[TR]
[TD]DEF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Overdue[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[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


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

Sub Vendor​

[/TD]
[TD]

Info1​

[/TD]
[TD]

Info2​

[/TD]
[TD]

Info3​

[/TD]
[TD]

Info4​

[/TD]
[TD]

Date1​

[/TD]
[TD]

Date2​

[/TD]
[TD]

SLA Formula​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

ABC​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

In SLA​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

DEF​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Overdue​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

GHI​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

No SLA​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

In SLA​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

123​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Overdue​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

ABC​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Overdue​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

DEF​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Overdue​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

345​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Overdue​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

678​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Overdue​

[/TD]
[/TR]
</TBODY>[/TABLE]



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.