Excel formula to return different results for all cells equal to, blank or count of a value
0
I am trying to create a formula that will look at a column of cells and do the following;
- if all cells blank then return no value
- if some cells "Unapproved" then count and show the number of these
- if all cells "Approved" then return "All Approved"
This is as far as got but will appreciate any help. This is an array; =IF(ISBLANK(AD14:AD23)," ",CONCATENATE(COUNTIF(AD14:AD23,"Unapproved")," Awaiting Approval"))
Look forward to hearing from you.
arrays excel if-statement formula
asked Nov 24 '18 at 20:25
peterreedpeterreed
238
add a comment |
0
I am trying to create a formula that will look at a column of cells and do the following;
- if all cells blank then return no value
- if some cells "Unapproved" then count and show the number of these
- if all cells "Approved" then return "All Approved"
This is as far as got but will appreciate any help. This is an array; =IF(ISBLANK(AD14:AD23)," ",CONCATENATE(COUNTIF(AD14:AD23,"Unapproved")," Awaiting Approval"))
Look forward to hearing from you.
arrays excel if-statement formula
asked Nov 24 '18 at 20:25
peterreedpeterreed
238
I should add that there won't be any fixed amount of Approved or Unapproved values.
– peterreed
Nov 24 '18 at 20:26
add a comment |
0
0
0
I am trying to create a formula that will look at a column of cells and do the following;
- if all cells blank then return no value
- if some cells "Unapproved" then count and show the number of these
- if all cells "Approved" then return "All Approved"
This is as far as got but will appreciate any help. This is an array; =IF(ISBLANK(AD14:AD23)," ",CONCATENATE(COUNTIF(AD14:AD23,"Unapproved")," Awaiting Approval"))
Look forward to hearing from you.
arrays excel if-statement formula
asked Nov 24 '18 at 20:25
peterreedpeterreed
238
I am trying to create a formula that will look at a column of cells and do the following;
- if all cells blank then return no value
- if some cells "Unapproved" then count and show the number of these
- if all cells "Approved" then return "All Approved"
This is as far as got but will appreciate any help. This is an array; =IF(ISBLANK(AD14:AD23)," ",CONCATENATE(COUNTIF(AD14:AD23,"Unapproved")," Awaiting Approval"))
Look forward to hearing from you.
arrays excel if-statement formula
arrays excel if-statement formula
asked Nov 24 '18 at 20:25
peterreedpeterreed
238
asked Nov 24 '18 at 20:25
peterreedpeterreed
238
asked Nov 24 '18 at 20:25
peterreedpeterreed
238
asked Nov 24 '18 at 20:25
peterreedpeterreed
238
asked Nov 24 '18 at 20:25
peterreedpeterreed
238
238
I should add that there won't be any fixed amount of Approved or Unapproved values.
– peterreed
Nov 24 '18 at 20:26
add a comment |
I should add that there won't be any fixed amount of Approved or Unapproved values.
– peterreed
Nov 24 '18 at 20:26
I should add that there won't be any fixed amount of Approved or Unapproved values.
– peterreed
Nov 24 '18 at 20:26
I should add that there won't be any fixed amount of Approved or Unapproved values.
– peterreed
Nov 24 '18 at 20:26
add a comment |
1 Answer
1
active
oldest
votes
1
You could use something like
=IF(COUNTIF($AD$14:$AD$23,"Approved")=ROWS($AD$14:$AD$23),"All approved",IF(COUNTIF($AD$14:$AD$23,"Unapproved"),COUNTIF($AD$14:$AD$23,"Unapproved"),IF(COUNTBLANK($AD$14:$AD$23)=ROWS($AD$14:$AD$23),"","undefined return value")))
If your blank cells are actually empty string literals (""), returned by formulas then I would update to:
=IF(COUNTIF($AD$14:$AD$23,"Approved")=ROWS($AD$14:$AD$23),"All approved",IF(COUNTIF($AD$14:$AD$23,"Unapproved"),COUNTIF($AD$14:$AD$23,"Unapproved"),IF(COUNTIF($AD$14:$AD$23,TEXT(,))=ROWS($AD$14:$AD$23),"","undefined return value")))
answered Nov 24 '18 at 22:02
QHarrQHarr
34.1k82044
Thanks. Thats very helpful. Unfortunately it always returned 'undefined return value' even if there was just 'Approved' in the column. So I changed it around a bit and it works, but it shows 'All Approved' if the column is blank. Do you have any suggestions for changes to this formula to get it to return blank if no values in the column? =IF(COUNTIF(AE14:AE23,"Approved")=COUNTA(AE14:AE23),"All Approved",IF(COUNTIF($AE$14:$AE$23,"Unapproved"),CONCATENATE(COUNTIF(AE14:AE23,"Unapproved")," Awaiting Approval"),""))
– peterreed
Nov 25 '18 at 9:26
The formula works for what you described. If there is a single Approved it will default to undefined return value as you haven't specified what you want when not all blanks or not all approved or 1 or more unapproved. Leave all blank and you should get "". Add 1 or more unapproved and you should get count, add approved in all cells and you should get All Approved. Try those first with my formula. Does it work as expected?
– QHarr
Nov 25 '18 at 9:42
I tried it again to be sure but any number of 'Approved' returns 'undefined return value' but it does a count on the 'Unapproved'.
– peterreed
Nov 25 '18 at 16:12
You are doing some thing wrong or somehow I am misunderstanding you. Let me share a workbook.
– QHarr
Nov 25 '18 at 16:15
Hello. I get 404 page not found to that link? Can you try another?
– peterreed
Nov 25 '18 at 16:42
|
show 6 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53462071%2fexcel-formula-to-return-different-results-for-all-cells-equal-to-blank-or-count%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You could use something like
=IF(COUNTIF($AD$14:$AD$23,"Approved")=ROWS($AD$14:$AD$23),"All approved",IF(COUNTIF($AD$14:$AD$23,"Unapproved"),COUNTIF($AD$14:$AD$23,"Unapproved"),IF(COUNTBLANK($AD$14:$AD$23)=ROWS($AD$14:$AD$23),"","undefined return value")))
If your blank cells are actually empty string literals (""), returned by formulas then I would update to:
=IF(COUNTIF($AD$14:$AD$23,"Approved")=ROWS($AD$14:$AD$23),"All approved",IF(COUNTIF($AD$14:$AD$23,"Unapproved"),COUNTIF($AD$14:$AD$23,"Unapproved"),IF(COUNTIF($AD$14:$AD$23,TEXT(,))=ROWS($AD$14:$AD$23),"","undefined return value")))
answered Nov 24 '18 at 22:02
QHarrQHarr
34.1k82044
Thanks. Thats very helpful. Unfortunately it always returned 'undefined return value' even if there was just 'Approved' in the column. So I changed it around a bit and it works, but it shows 'All Approved' if the column is blank. Do you have any suggestions for changes to this formula to get it to return blank if no values in the column? =IF(COUNTIF(AE14:AE23,"Approved")=COUNTA(AE14:AE23),"All Approved",IF(COUNTIF($AE$14:$AE$23,"Unapproved"),CONCATENATE(COUNTIF(AE14:AE23,"Unapproved")," Awaiting Approval"),""))
– peterreed
Nov 25 '18 at 9:26
The formula works for what you described. If there is a single Approved it will default to undefined return value as you haven't specified what you want when not all blanks or not all approved or 1 or more unapproved. Leave all blank and you should get "". Add 1 or more unapproved and you should get count, add approved in all cells and you should get All Approved. Try those first with my formula. Does it work as expected?
– QHarr
Nov 25 '18 at 9:42
I tried it again to be sure but any number of 'Approved' returns 'undefined return value' but it does a count on the 'Unapproved'.
– peterreed
Nov 25 '18 at 16:12
You are doing some thing wrong or somehow I am misunderstanding you. Let me share a workbook.
– QHarr
Nov 25 '18 at 16:15
Hello. I get 404 page not found to that link? Can you try another?
– peterreed
Nov 25 '18 at 16:42
|
show 6 more comments
1
You could use something like
=IF(COUNTIF($AD$14:$AD$23,"Approved")=ROWS($AD$14:$AD$23),"All approved",IF(COUNTIF($AD$14:$AD$23,"Unapproved"),COUNTIF($AD$14:$AD$23,"Unapproved"),IF(COUNTBLANK($AD$14:$AD$23)=ROWS($AD$14:$AD$23),"","undefined return value")))
If your blank cells are actually empty string literals (""), returned by formulas then I would update to:
=IF(COUNTIF($AD$14:$AD$23,"Approved")=ROWS($AD$14:$AD$23),"All approved",IF(COUNTIF($AD$14:$AD$23,"Unapproved"),COUNTIF($AD$14:$AD$23,"Unapproved"),IF(COUNTIF($AD$14:$AD$23,TEXT(,))=ROWS($AD$14:$AD$23),"","undefined return value")))
answered Nov 24 '18 at 22:02
QHarrQHarr
34.1k82044
Thanks. Thats very helpful. Unfortunately it always returned 'undefined return value' even if there was just 'Approved' in the column. So I changed it around a bit and it works, but it shows 'All Approved' if the column is blank. Do you have any suggestions for changes to this formula to get it to return blank if no values in the column? =IF(COUNTIF(AE14:AE23,"Approved")=COUNTA(AE14:AE23),"All Approved",IF(COUNTIF($AE$14:$AE$23,"Unapproved"),CONCATENATE(COUNTIF(AE14:AE23,"Unapproved")," Awaiting Approval"),""))
– peterreed
Nov 25 '18 at 9:26
The formula works for what you described. If there is a single Approved it will default to undefined return value as you haven't specified what you want when not all blanks or not all approved or 1 or more unapproved. Leave all blank and you should get "". Add 1 or more unapproved and you should get count, add approved in all cells and you should get All Approved. Try those first with my formula. Does it work as expected?
– QHarr
Nov 25 '18 at 9:42
I tried it again to be sure but any number of 'Approved' returns 'undefined return value' but it does a count on the 'Unapproved'.
– peterreed
Nov 25 '18 at 16:12
You are doing some thing wrong or somehow I am misunderstanding you. Let me share a workbook.
– QHarr
Nov 25 '18 at 16:15
Hello. I get 404 page not found to that link? Can you try another?
– peterreed
Nov 25 '18 at 16:42
|
show 6 more comments
1
1
1
You could use something like
=IF(COUNTIF($AD$14:$AD$23,"Approved")=ROWS($AD$14:$AD$23),"All approved",IF(COUNTIF($AD$14:$AD$23,"Unapproved"),COUNTIF($AD$14:$AD$23,"Unapproved"),IF(COUNTBLANK($AD$14:$AD$23)=ROWS($AD$14:$AD$23),"","undefined return value")))
If your blank cells are actually empty string literals (""), returned by formulas then I would update to:
=IF(COUNTIF($AD$14:$AD$23,"Approved")=ROWS($AD$14:$AD$23),"All approved",IF(COUNTIF($AD$14:$AD$23,"Unapproved"),COUNTIF($AD$14:$AD$23,"Unapproved"),IF(COUNTIF($AD$14:$AD$23,TEXT(,))=ROWS($AD$14:$AD$23),"","undefined return value")))
answered Nov 24 '18 at 22:02
QHarrQHarr
34.1k82044
You could use something like
=IF(COUNTIF($AD$14:$AD$23,"Approved")=ROWS($AD$14:$AD$23),"All approved",IF(COUNTIF($AD$14:$AD$23,"Unapproved"),COUNTIF($AD$14:$AD$23,"Unapproved"),IF(COUNTBLANK($AD$14:$AD$23)=ROWS($AD$14:$AD$23),"","undefined return value")))
If your blank cells are actually empty string literals (""), returned by formulas then I would update to:
=IF(COUNTIF($AD$14:$AD$23,"Approved")=ROWS($AD$14:$AD$23),"All approved",IF(COUNTIF($AD$14:$AD$23,"Unapproved"),COUNTIF($AD$14:$AD$23,"Unapproved"),IF(COUNTIF($AD$14:$AD$23,TEXT(,))=ROWS($AD$14:$AD$23),"","undefined return value")))
answered Nov 24 '18 at 22:02
QHarrQHarr
34.1k82044
edited Nov 25 '18 at 21:30
answered Nov 24 '18 at 22:02
QHarrQHarr
34.1k82044
answered Nov 24 '18 at 22:02
QHarrQHarr
34.1k82044
answered Nov 24 '18 at 22:02
QHarrQHarr
34.1k82044
34.1k82044
Thanks. Thats very helpful. Unfortunately it always returned 'undefined return value' even if there was just 'Approved' in the column. So I changed it around a bit and it works, but it shows 'All Approved' if the column is blank. Do you have any suggestions for changes to this formula to get it to return blank if no values in the column? =IF(COUNTIF(AE14:AE23,"Approved")=COUNTA(AE14:AE23),"All Approved",IF(COUNTIF($AE$14:$AE$23,"Unapproved"),CONCATENATE(COUNTIF(AE14:AE23,"Unapproved")," Awaiting Approval"),""))
– peterreed
Nov 25 '18 at 9:26
The formula works for what you described. If there is a single Approved it will default to undefined return value as you haven't specified what you want when not all blanks or not all approved or 1 or more unapproved. Leave all blank and you should get "". Add 1 or more unapproved and you should get count, add approved in all cells and you should get All Approved. Try those first with my formula. Does it work as expected?
– QHarr
Nov 25 '18 at 9:42
I tried it again to be sure but any number of 'Approved' returns 'undefined return value' but it does a count on the 'Unapproved'.
– peterreed
Nov 25 '18 at 16:12
You are doing some thing wrong or somehow I am misunderstanding you. Let me share a workbook.
– QHarr
Nov 25 '18 at 16:15
Hello. I get 404 page not found to that link? Can you try another?
– peterreed
Nov 25 '18 at 16:42
|
show 6 more comments
Thanks. Thats very helpful. Unfortunately it always returned 'undefined return value' even if there was just 'Approved' in the column. So I changed it around a bit and it works, but it shows 'All Approved' if the column is blank. Do you have any suggestions for changes to this formula to get it to return blank if no values in the column? =IF(COUNTIF(AE14:AE23,"Approved")=COUNTA(AE14:AE23),"All Approved",IF(COUNTIF($AE$14:$AE$23,"Unapproved"),CONCATENATE(COUNTIF(AE14:AE23,"Unapproved")," Awaiting Approval"),""))
– peterreed
Nov 25 '18 at 9:26
The formula works for what you described. If there is a single Approved it will default to undefined return value as you haven't specified what you want when not all blanks or not all approved or 1 or more unapproved. Leave all blank and you should get "". Add 1 or more unapproved and you should get count, add approved in all cells and you should get All Approved. Try those first with my formula. Does it work as expected?
– QHarr
Nov 25 '18 at 9:42
I tried it again to be sure but any number of 'Approved' returns 'undefined return value' but it does a count on the 'Unapproved'.
– peterreed
Nov 25 '18 at 16:12
You are doing some thing wrong or somehow I am misunderstanding you. Let me share a workbook.
– QHarr
Nov 25 '18 at 16:15
Hello. I get 404 page not found to that link? Can you try another?
– peterreed
Nov 25 '18 at 16:42
Thanks. Thats very helpful. Unfortunately it always returned 'undefined return value' even if there was just 'Approved' in the column. So I changed it around a bit and it works, but it shows 'All Approved' if the column is blank. Do you have any suggestions for changes to this formula to get it to return blank if no values in the column? =IF(COUNTIF(AE14:AE23,"Approved")=COUNTA(AE14:AE23),"All Approved",IF(COUNTIF($AE$14:$AE$23,"Unapproved"),CONCATENATE(COUNTIF(AE14:AE23,"Unapproved")," Awaiting Approval"),""))
– peterreed
Nov 25 '18 at 9:26
Thanks. Thats very helpful. Unfortunately it always returned 'undefined return value' even if there was just 'Approved' in the column. So I changed it around a bit and it works, but it shows 'All Approved' if the column is blank. Do you have any suggestions for changes to this formula to get it to return blank if no values in the column? =IF(COUNTIF(AE14:AE23,"Approved")=COUNTA(AE14:AE23),"All Approved",IF(COUNTIF($AE$14:$AE$23,"Unapproved"),CONCATENATE(COUNTIF(AE14:AE23,"Unapproved")," Awaiting Approval"),""))
– peterreed
Nov 25 '18 at 9:26
The formula works for what you described. If there is a single Approved it will default to undefined return value as you haven't specified what you want when not all blanks or not all approved or 1 or more unapproved. Leave all blank and you should get "". Add 1 or more unapproved and you should get count, add approved in all cells and you should get All Approved. Try those first with my formula. Does it work as expected?
– QHarr
Nov 25 '18 at 9:42
The formula works for what you described. If there is a single Approved it will default to undefined return value as you haven't specified what you want when not all blanks or not all approved or 1 or more unapproved. Leave all blank and you should get "". Add 1 or more unapproved and you should get count, add approved in all cells and you should get All Approved. Try those first with my formula. Does it work as expected?
– QHarr
Nov 25 '18 at 9:42
I tried it again to be sure but any number of 'Approved' returns 'undefined return value' but it does a count on the 'Unapproved'.
– peterreed
Nov 25 '18 at 16:12
I tried it again to be sure but any number of 'Approved' returns 'undefined return value' but it does a count on the 'Unapproved'.
– peterreed
Nov 25 '18 at 16:12
You are doing some thing wrong or somehow I am misunderstanding you. Let me share a workbook.
– QHarr
Nov 25 '18 at 16:15
You are doing some thing wrong or somehow I am misunderstanding you. Let me share a workbook.
– QHarr
Nov 25 '18 at 16:15
Hello. I get 404 page not found to that link? Can you try another?
– peterreed
Nov 25 '18 at 16:42
Hello. I get 404 page not found to that link? Can you try another?
– peterreed
Nov 25 '18 at 16:42
|
show 6 more comments
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53462071%2fexcel-formula-to-return-different-results-for-all-cells-equal-to-blank-or-count%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I should add that there won't be any fixed amount of Approved or Unapproved values.
– peterreed
Nov 24 '18 at 20:26