countif but have two criteria

[Guest]

I have a cell range that has 3 different types of cells in it. It's about
training, some have date or period when training was completed, some have
"due" plus date or period, and some are blank, yet to be filled. I need to
add the blank cells to the due ones, tried countif but only one criteria
allowed, tried sumproduct (--(A1:A500="*due*"),--(A1:A500="")) didn't work
(value error in part two). How do I add the blanks and the dues together. Any
help appreciated.

 

[Dave Peterson]

=countif(a1:a500,"*due*") + countif(a1:a500,"")
or
=sum(Countif(a1:a500,{"*due*",""}))

 

[Bob Phillips]

It should be

=SUMPRODUCT(--((ISNUMBER(FIND("due",A1:A500)))+(A1:A500="")))

Dave, your formula omits cells that have never had a value, only counts
those that had a value then cleared.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thank you, work a treat, I have spent days fiddling, very many thanks

 

[Dave Peterson]

You sure?

I started a new workbook and put this in D1 of Sheet1 (no other changes):
=countif(a1:a500,"*due*") + countif(a1:a500,"")

I got 500 back.

Same with:
=sum(Countif(a1:a500,{"*due*",""}))

 

[Guest]

Try this

=SUMPRODUCT(--(A1:A500={"*due*",""})

 

[Bob Phillips]

There is a missing ), and it doesn't work anyway with the wildcard. See my
response earlier.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Dave Peterson]

And see my followup to your response.

There is a missing ), and it doesn't work anyway with the wildcard. See my
response earlier.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Epinn]

Dave,

I think Bob's most recent response "There is a missing ), ......." is for the other poster mama no teeth and not you. Once all this is sorted out, I'll digest all the posts.

I think I am "brave" to make a comment when there is a "debate." Will keep quiet now.

Epinn

And see my followup to your response.

 

[Dave Peterson]

Ahh, but one of my suggestions was the same as mama's (with the correction).

And both of them still worked for me today!

 

[Guest]

Thanks again, it still works for me, too, but i have another one, if I may.
All these names are split up into different departments, some have only 2 or
3, largest one has 150. Some depts are subdivided, too, i.e. Dept 1 has AB+C,
Dept 2 has ABC+D. I am trying to cut and paste the totals into a table on
sheet 3 of my workbook but it comes upp with
=countif(#REF!,"*due*")+countif(#REF!,"")
what am I doing wrong?

Ahh, but one of my suggestions was the same as mama's (with the correction).

And both of them still worked for me today!

 

[Dave Peterson]

If you want the values--not the formulas, you could
Select the range to copy
If you have hidden rows/columns that you don't want to copy|paste
edit|goto|special|visible cells only

Off to its new home
edit|paste special|Values

Thanks again, it still works for me, too, but i have another one, if I may.
All these names are split up into different departments, some have only 2 or
3, largest one has 150. Some depts are subdivided, too, i.e. Dept 1 has AB+C,
Dept 2 has ABC+D. I am trying to cut and paste the totals into a table on
sheet 3 of my workbook but it comes upp with
=countif(#REF!,"*due*")+countif(#REF!,"")
what am I doing wrong?

 

[Guest]

what I have looks as follows:
column A column B
Dept 1A training
Name 01/01
Name due wk 20
Name
Name due wk 25
Name 05/06
total 3 (as I am only counting not completed)
Dept 1B
Name 01/01
Name
Name due wk20
Name due wk 36
total 3 (as above)
Dept 2
etc etc
Now I would like to add the two totals for dept 1 (regardless of
subdivision) into a table like this on sheet 3 of my workbook:

Head count Target Reviews %
total figure due
Dept 1 9 9 6 (the
2 totals added up)
etc
I tried =sum(A6+A12) but it will not transfer from sheet 1 to sheet 3 so
there's my problem, what formula do I need to pull the data across?
Many thanks for your help, I think you guys are doing a brilliant job.

If you want the values--not the formulas, you could
Select the range to copy
If you have hidden rows/columns that you don't want to copy|paste
edit|goto|special|visible cells only

Off to its new home
edit|paste special|Values

 

[Dave Peterson]

Edit|Paste special|Values will paste the values and not the formulas.

But I would take some time to set up my data so that it's easier to work with.

I'd use column A for Department. Then column B for Subdivision, Column C for
Name...

Then I could sort by those fields and use Data|subtotals to get my counts.

what I have looks as follows:
column A column B
Dept 1A training
Name 01/01
Name due wk 20
Name
Name due wk 25
Name 05/06
total 3 (as I am only counting not completed)
Dept 1B
Name 01/01
Name
Name due wk20
Name due wk 36
total 3 (as above)
Dept 2
etc etc
Now I would like to add the two totals for dept 1 (regardless of
subdivision) into a table like this on sheet 3 of my workbook:

Head count Target Reviews %
total figure due
Dept 1 9 9 6 (the
2 totals added up)
etc
I tried =sum(A6+A12) but it will not transfer from sheet 1 to sheet 3 so
there's my problem, what formula do I need to pull the data across?
Many thanks for your help, I think you guys are doing a brilliant job.

 

[Guest]

Thank you again, for your help and time spent on this.