Simple COUNT formula not working across sheets in Excel - Works in Google sheets2019 Community Moderator ElectionShortcut to Apply a Formula to an Entire Column in ExcelHow do I get my formula to always reference to the last sheet?Formula in Excel to count occurrences of substrings within stringsHow are Google Spreadsheet array formulas different from Excel (count until sum reached)COUNTIFS across two sheetsExcel formula to count how many times work was carried out on a weekend between the hours of 17:00 and 19:00Query Formula on Sheets Not Working In Excel“Indirect” Excel function not working as intended on Google SheetsFormula referencing cells as index not workingExcel Formula | Count all values that have the “keyword” in the adjacent cell in the row
I've given my players a lot of magic items. Is it reasonable for me to give them harder encounters?
What does it take to become a wilderness skills guide as a business?
Vector-transposing function
If nine coins are tossed, what is the probability that the number of heads is even?
Can Witch Sight see through Mirror Image?
Does the US political system, in principle, allow for a no-party system?
Boss Telling direct supervisor I snitched
What exactly is the meaning of "fine wine"?
Do I need a return ticket to Canada if I'm a Japanese National?
What can I do if someone tampers with my SSH public key?
What does *dead* mean in *What do you mean, dead?*?
PTIJ: Sport in the Torah
Insult for someone who "doesn't know anything"
Use Mercury as quenching liquid for swords?
I am the light that shines in the dark
Why is there an extra space when I type "ls" on the Desktop?
Tool for measuring readability of English text
How would an energy-based "projectile" blow up a spaceship?
Draw this image in the TIKZ package
Is this Paypal Github SDK reference really a dangerous site?
Rationale to prefer local variables over instance variables?
How to educate team mate to take screenshots for bugs with out unwanted stuff
What is better: yes / no radio, or simple checkbox?
I am the person who abides by rules but breaks the rules . Who am I
Simple COUNT formula not working across sheets in Excel - Works in Google sheets
2019 Community Moderator ElectionShortcut to Apply a Formula to an Entire Column in ExcelHow do I get my formula to always reference to the last sheet?Formula in Excel to count occurrences of substrings within stringsHow are Google Spreadsheet array formulas different from Excel (count until sum reached)COUNTIFS across two sheetsExcel formula to count how many times work was carried out on a weekend between the hours of 17:00 and 19:00Query Formula on Sheets Not Working In Excel“Indirect” Excel function not working as intended on Google SheetsFormula referencing cells as index not workingExcel Formula | Count all values that have the “keyword” in the adjacent cell in the row
Here's my entire layout:
I have three sheets. In my second sheet, called 'data', I'm using the formula below. I have a Google spreadsheet I'm using that is correctly counting the number.
=COUNTIFS(raw!$B:$B,">="&$A51,raw!$B:$B,"<"&$A52)
The formula should (or at least is in Google sheets) counting any time range that fits between two values. In this case, between 6:00 AM and 6:30 AM.
However, in Excel I'm just getting a 0 count.
Here's a snippet of my 'data' sheet that I'm using the formula in:
Here's a snipper of my sheet called 'raw' clearly showing a time fitting the criteria..
Any advice?
excel excel-formula spreadsheet
asked 2 days ago
Jacob JohnsonJacob Johnson
1741114
|
show 2 more comments
Here's my entire layout:
I have three sheets. In my second sheet, called 'data', I'm using the formula below. I have a Google spreadsheet I'm using that is correctly counting the number.
=COUNTIFS(raw!$B:$B,">="&$A51,raw!$B:$B,"<"&$A52)
The formula should (or at least is in Google sheets) counting any time range that fits between two values. In this case, between 6:00 AM and 6:30 AM.
However, in Excel I'm just getting a 0 count.
Here's a snippet of my 'data' sheet that I'm using the formula in:
Here's a snipper of my sheet called 'raw' clearly showing a time fitting the criteria..
Any advice?
excel excel-formula spreadsheet
asked 2 days ago
Jacob JohnsonJacob Johnson
1741114
1
look to me that your data inrawis text that looks like a time not an actual time.
– Scott Craner
2 days ago
@ScottCraner what do you mean? because according to Excel they're in the same "Time" format.
– Jacob Johnson
2 days ago
Try changing the format to General. Does it change the output? Text cannot have a number format. If it does not change to a decimal it is text that looks like a time and not a true time.
– Scott Craner
2 days ago
1
Then as I stated the "times" in the raw sheet are stored as text not a number. You will need to convert those to actual times for the count to work. This is not an issue with the formula but an issue with the data. Fix the data and the formula will work.
– Scott Craner
2 days ago
2
For conversion you can use multiplication. If cell A1 contains a time or text that represents a time (e.g. 06:00:00) then the formula A1*1 will return the corresponding numeric value (e.g. 0.25). Would agree with Scott in that it looks like a format issue.
– Jormund
2 days ago
|
show 2 more comments
Here's my entire layout:
I have three sheets. In my second sheet, called 'data', I'm using the formula below. I have a Google spreadsheet I'm using that is correctly counting the number.
=COUNTIFS(raw!$B:$B,">="&$A51,raw!$B:$B,"<"&$A52)
The formula should (or at least is in Google sheets) counting any time range that fits between two values. In this case, between 6:00 AM and 6:30 AM.
However, in Excel I'm just getting a 0 count.
Here's a snippet of my 'data' sheet that I'm using the formula in:
Here's a snipper of my sheet called 'raw' clearly showing a time fitting the criteria..
Any advice?
excel excel-formula spreadsheet
asked 2 days ago
Jacob JohnsonJacob Johnson
1741114
Here's my entire layout:
I have three sheets. In my second sheet, called 'data', I'm using the formula below. I have a Google spreadsheet I'm using that is correctly counting the number.
=COUNTIFS(raw!$B:$B,">="&$A51,raw!$B:$B,"<"&$A52)
The formula should (or at least is in Google sheets) counting any time range that fits between two values. In this case, between 6:00 AM and 6:30 AM.
However, in Excel I'm just getting a 0 count.
Here's a snippet of my 'data' sheet that I'm using the formula in:
Here's a snipper of my sheet called 'raw' clearly showing a time fitting the criteria..
Any advice?
excel excel-formula spreadsheet
excel excel-formula spreadsheet
asked 2 days ago
Jacob JohnsonJacob Johnson
1741114
asked 2 days ago
Jacob JohnsonJacob Johnson
1741114
asked 2 days ago
Jacob JohnsonJacob Johnson
1741114
asked 2 days ago
Jacob JohnsonJacob Johnson
1741114
asked 2 days ago
Jacob JohnsonJacob Johnson
1741114
1741114
1
look to me that your data inrawis text that looks like a time not an actual time.
– Scott Craner
2 days ago
@ScottCraner what do you mean? because according to Excel they're in the same "Time" format.
– Jacob Johnson
2 days ago
Try changing the format to General. Does it change the output? Text cannot have a number format. If it does not change to a decimal it is text that looks like a time and not a true time.
– Scott Craner
2 days ago
1
Then as I stated the "times" in the raw sheet are stored as text not a number. You will need to convert those to actual times for the count to work. This is not an issue with the formula but an issue with the data. Fix the data and the formula will work.
– Scott Craner
2 days ago
2
For conversion you can use multiplication. If cell A1 contains a time or text that represents a time (e.g. 06:00:00) then the formula A1*1 will return the corresponding numeric value (e.g. 0.25). Would agree with Scott in that it looks like a format issue.
– Jormund
2 days ago
|
show 2 more comments
1
look to me that your data inrawis text that looks like a time not an actual time.
– Scott Craner
2 days ago
@ScottCraner what do you mean? because according to Excel they're in the same "Time" format.
– Jacob Johnson
2 days ago
Try changing the format to General. Does it change the output? Text cannot have a number format. If it does not change to a decimal it is text that looks like a time and not a true time.
– Scott Craner
2 days ago
1
Then as I stated the "times" in the raw sheet are stored as text not a number. You will need to convert those to actual times for the count to work. This is not an issue with the formula but an issue with the data. Fix the data and the formula will work.
– Scott Craner
2 days ago
2
For conversion you can use multiplication. If cell A1 contains a time or text that represents a time (e.g. 06:00:00) then the formula A1*1 will return the corresponding numeric value (e.g. 0.25). Would agree with Scott in that it looks like a format issue.
– Jormund
2 days ago
1
1
look to me that your data in
raw is text that looks like a time not an actual time.– Scott Craner
2 days ago
look to me that your data in
raw is text that looks like a time not an actual time.– Scott Craner
2 days ago
@ScottCraner what do you mean? because according to Excel they're in the same "Time" format.
– Jacob Johnson
2 days ago
@ScottCraner what do you mean? because according to Excel they're in the same "Time" format.
– Jacob Johnson
2 days ago
Try changing the format to General. Does it change the output? Text cannot have a number format. If it does not change to a decimal it is text that looks like a time and not a true time.
– Scott Craner
2 days ago
Try changing the format to General. Does it change the output? Text cannot have a number format. If it does not change to a decimal it is text that looks like a time and not a true time.
– Scott Craner
2 days ago
1
1
Then as I stated the "times" in the raw sheet are stored as text not a number. You will need to convert those to actual times for the count to work. This is not an issue with the formula but an issue with the data. Fix the data and the formula will work.
– Scott Craner
2 days ago
Then as I stated the "times" in the raw sheet are stored as text not a number. You will need to convert those to actual times for the count to work. This is not an issue with the formula but an issue with the data. Fix the data and the formula will work.
– Scott Craner
2 days ago
2
2
For conversion you can use multiplication. If cell A1 contains a time or text that represents a time (e.g. 06:00:00) then the formula A1*1 will return the corresponding numeric value (e.g. 0.25). Would agree with Scott in that it looks like a format issue.
– Jormund
2 days ago
For conversion you can use multiplication. If cell A1 contains a time or text that represents a time (e.g. 06:00:00) then the formula A1*1 will return the corresponding numeric value (e.g. 0.25). Would agree with Scott in that it looks like a format issue.
– Jormund
2 days ago
|
show 2 more comments
0
active
oldest
votes
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
);
);
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%2f55027027%2fsimple-count-formula-not-working-across-sheets-in-excel-works-in-google-sheets%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
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%2f55027027%2fsimple-count-formula-not-working-across-sheets-in-excel-works-in-google-sheets%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
1
look to me that your data in
rawis text that looks like a time not an actual time.– Scott Craner
2 days ago
@ScottCraner what do you mean? because according to Excel they're in the same "Time" format.
– Jacob Johnson
2 days ago
Try changing the format to General. Does it change the output? Text cannot have a number format. If it does not change to a decimal it is text that looks like a time and not a true time.
– Scott Craner
2 days ago
1
Then as I stated the "times" in the raw sheet are stored as text not a number. You will need to convert those to actual times for the count to work. This is not an issue with the formula but an issue with the data. Fix the data and the formula will work.
– Scott Craner
2 days ago
2
For conversion you can use multiplication. If cell A1 contains a time or text that represents a time (e.g. 06:00:00) then the formula A1*1 will return the corresponding numeric value (e.g. 0.25). Would agree with Scott in that it looks like a format issue.
– Jormund
2 days ago