| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
countif but have two criteria
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. |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
countif but have two criteria
=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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
countif but have two criteria
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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
countif but have two criteria
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. |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
countif but have two criteria
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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
countif but have two criteria
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. |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
countif but have two criteria
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. |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
countif but have two criteria
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 |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
countif but have two criteria
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 |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
countif but have two criteria
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF statement with 3 criteria checks | Excel Discussion (Misc queries) | |||
| COUNTIF with two criteria | Excel Worksheet Functions | |||
| Countif w/ Multiple Criteria | Excel Worksheet Functions | |||
| Countif using format criteria....not number criteria? | Excel Worksheet Functions | |||
| counting cells (COUNTIF) based on two or more criteria | Excel Worksheet Functions | |||
Hybrid Mode
