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.

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





Ani63 wrote:

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

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)

"Ani63" wrote in message
...
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.

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

"Dave Peterson" wrote:

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





Ani63 wrote:

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

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*",""}))



Bob Phillips wrote:

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)

"Ani63" wrote in message
...
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

Try this

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

"Ani63" wrote:

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.

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)

"mama no teeth" wrote in message
...
Try this

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

"Ani63" wrote:

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.

And see my followup to your response.

Bob Phillips wrote:

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)

"mama no teeth" wrote in message
...
Try this

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

"Ani63" wrote:

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

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

"Dave Peterson" wrote in message ...
And see my followup to your response.

Bob Phillips wrote:

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)

"mama no teeth" wrote in message
...
Try this

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

"Ani63" wrote:

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

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

And both of them still worked for me today!

Epinn wrote:

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

"Dave Peterson" wrote in message ...
And see my followup to your response.

Bob Phillips wrote:

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)

"mama no teeth" wrote in message
...
Try this

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

"Ani63" wrote:

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

--

Dave Peterson

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?

"Dave Peterson" wrote:

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

And both of them still worked for me today!

Epinn wrote:

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

"Dave Peterson" wrote in message ...
And see my followup to your response.

Bob Phillips wrote:

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)

"mama no teeth" wrote in message
...
Try this

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

"Ani63" wrote:

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

--

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



Ani63 wrote:

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?

"Dave Peterson" wrote:

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

And both of them still worked for me today!

Epinn wrote:

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

"Dave Peterson" wrote in message ...
And see my followup to your response.

Bob Phillips wrote:

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)

"mama no teeth" wrote in message
...
Try this

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

"Ani63" wrote:

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

--

Dave Peterson


--

Dave Peterson

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.

"Dave Peterson" wrote:

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



Ani63 wrote:

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?

"Dave Peterson" wrote:

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

And both of them still worked for me today!

Epinn wrote:

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

"Dave Peterson" wrote in message ...
And see my followup to your response.

Bob Phillips wrote:

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)

"mama no teeth" wrote in message
...
Try this

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

"Ani63" wrote:

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

--

Dave Peterson


--

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.



Ani63 wrote:

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.

"Dave Peterson" wrote:

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



Ani63 wrote:

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?

"Dave Peterson" wrote:

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

And both of them still worked for me today!

Epinn wrote:

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

"Dave Peterson" wrote in message ...
And see my followup to your response.

Bob Phillips wrote:

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)

"mama no teeth" wrote in message
...
Try this

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

"Ani63" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

"Dave Peterson" wrote:

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.



Ani63 wrote:

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.

"Dave Peterson" wrote:

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



Ani63 wrote:

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?

"Dave Peterson" wrote:

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

And both of them still worked for me today!

Epinn wrote:

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

"Dave Peterson" wrote in message ...
And see my followup to your response.

Bob Phillips wrote:

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)

"mama no teeth" wrote in message
...
Try this

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

"Ani63" wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson