Can I use a Reference inside a SUMIF or COUNTIF Function?

Albie

unread,

Nov 13, 2005, 4:50:06 PM11/13/05

to

I am trying to utilize a SUMIF function that references a cell to provide the
the criteria, so that I can changed the criteria inside a optimization or
scenario. My functions are all returning zero when I use the cell reference,
but when I use the actual value, it returns what it should? Any way I can
get around this?

DOR

unread,

Nov 13, 2005, 5:12:38 PM11/13/05

to

It should work properly if you are looking for an equal condition.
However, if you are looking for >,<,or <>, then you need something like

=SUMIF(CritRange,">"&A1,SumRange)

where your criterion value is in A1, if you are looking for an
inequality. You put whatever inequality sign you need between the
quotes.

HTH

DOR

Ron Rosenfeld

unread,

Nov 13, 2005, 5:14:30 PM11/13/05

to

On Sun, 13 Nov 2005 13:50:06 -0800, "Albie" <Al...@discussions.microsoft.com>
wrote:

It's just a syntax issue:

=SUMIF(rng_to_check, "=" & cell_ref, rng_to_sum)

--ron

Albie

unread,

Nov 13, 2005, 5:20:03 PM11/13/05

to

Thanks! Works wonders!

Albie

unread,

Nov 13, 2005, 5:21:02 PM11/13/05

to

Thanks! Works great. I was suspecting syntax but as usual, the help files
didn't help

Laura

unread,

Sep 29, 2009, 2:55:02 PM9/29/09

to

I am trying to use a countif formula to count column a - rows 6-99 if
between10%and 25% AND if column b =DS.

can you help - i have tried the sumproduct - might not have the right
formula to put in but I tried and i have tried the function and several other
suggestions the help provided.

I would greatly appreciate it. Thanks

T. Valko

unread,

Sep 29, 2009, 6:47:33 PM9/29/09

to

Try this:

=SUMPRODUCT(--(A6:A99>=10%),--(A6:A99<=25%),--(B6:B99="DS"))

--
Biff
Microsoft Excel MVP

"Laura" <La...@discussions.microsoft.com> wrote in message
news:CF07DFED-E5B7-432F...@microsoft.com...

Laura

unread,

Sep 30, 2009, 7:25:01 AM9/30/09

to

it works!!! Thank you so very much.

T. Valko

unread,

Sep 30, 2009, 11:47:08 AM9/30/09

to

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Laura" <La...@discussions.microsoft.com> wrote in message

news:E92AFFAA-4C8C-4352...@microsoft.com...

Dan

unread,

Mar 9, 2010, 7:40:01 PM3/9/10

to

I have a similar sort of issue.

I have spent a bit of time searching, but not finding exactly what I need.

I want to turn the cell reference inside of the COUNTIF function into a
wildcard lookup to count the number of times a particular string appears
within the given range.

ie.

CRITERIA - A1=ABC
RANGE - A4:S100 Some cells with data of "ABC" alone, others have "ABC, DEF"
OR "XYZ, DEF, ABC" etc etc

In essence what I what to do is =COUNTIF(A4:S100, *A1*), but obviously I
cannot put * into the cell reference like I would if I simply typed the
string ie =COUNTIF(A4:S100, "*ABC*")

This may be something quite easy and I'm going about it the wrong way with
the wrong function.

Regards

ginger82

unread,

Apr 5, 2010, 12:19:23 PM4/5/10

to

Hi Im having a similar issue, I'm trying to count numbers within a range and
then substract that number to another countif formula so something like this:

=COUNTIF('Data Report'!$N$14:$N$2857,"O-PFS"-(COUNTIF('Data
Report'!$O$14:$O$2857,">75<85")))

so N coulmn has codes like"o-pfs" and O columns has numbers, in a separate
sheet I need to count the number of N with "o-pfs" minus O column within 75
and 85..
does this make sense? my formula gives me 0 in return???
thanks

Bob Phillips

unread,

Apr 5, 2010, 12:40:32 PM4/5/10

to

=COUNTIF('Data Report'!$N$14:$N$2857,"O-PFS"-

(COUNTIF('Data Report'!$O$14:$O$2857,">75")-COUNTIF('Data
Report'!$O$14:$O$2857,">=85"))

--

HTH

Bob

"ginger82" <ging...@discussions.microsoft.com> wrote in message
news:5F3075E7-2B66-4135...@microsoft.com...

IlDavo

unread,

Apr 29, 2010, 1:34:01 PM4/29/10

to

"Dan" wrote:

Try: =COUNTIF(A4:S100,CONCATENATE("*",A1,"*"))

Glyn

unread,

May 14, 2010, 11:26:06 AM5/14/10

to

Hello,

A slightly different query for you..

I'm trying to use a countif on a particular "calendar", which shows days
across the sheet and named individuals down the sheet. The main content
shows what piece of work day by day each person is planned to be working on.
All "task" values entered are selected from drop-down lists the data for
which are sourced from a separate range in a second sheet (call it Jobs for
argument's sake).. so for example:

10/05 11/05 12/05 13/05
name1 defects defects defects P2 Build
name2 P1 Build CCR123 CCR123 P1 Build
name3 CCR49 CCR49 P2 Build P2 Build

We currently use some summary COUNTIF functions at the bottom of the first
worksheet to sum up:
- the number of people in a given day working on Project 1 build;
- the number of people working on Project 2 build;
- the number of people working on defects etc.

But some of the work (e.g. change control requests (CCRs)) may relate to P1
build or P2 build. So what I want to do (rather than amending the formula
throughout the year as new CCRs come in) is for each daily column, count up
all entries that show "P1 Build" AND in the same formula count any CCRs which
are categorised as "P1 Build" in the lookup table on the "Jobs" tab.

The range on the second tab looks like this:

Deliverable Workstream
CCR123 P1 Build
CCR456 P2 Build
CCR789 P1 Build
CCR889 etc. P2 Build
defects defects

etc.

The formula I'm currently trying (which is failing to do the lookup) is as
follows:

=COUNTIF(CW7:CW76,"P2 Build") +
COUNTIF(CW7:CW76,VLOOKUP(CW7:CW76,Jobs!A59:A122,2,FALSE)&"=P2 Build")

So count all instances of "P2 Build" in the main sheet, range CW7:CW76.
Add to this any entries from the main sheet for which the second column on
the Jobs sheet is set to "P2 Build".

Any suggestions appreciated...

Thank you

Glyn