Number with Apostrophe in front doesn't know if it is a number or text!

[hcgood]

Hi. In my spreadsheet designed to handle a project plan, if a stage was completed in the previous phase we type an apostrophe in front of the year, e.g. '2022 so it doesn't affect calculations for the current project, but still retains the historical information. In a nearby cell if I type the "IsText" function on this cell it correctly shows as True because of the apostrophe.

However, if I include this '2022 column in a SumIf formula and the criteria is 2022 there is a problem. It matches the '2022 with the 2022, thinks the '2022 is a number, sums as part of the total and throws off my calculations. Only if I enter a second apostrophe in front of the '2022 (appearing as ''2022) does it now recognize it as text and calculates correctly because it now sees it as text.

I don't understand why in one usage it sees it as text with only one apostrophe and works correctly, but with the SumIf it considers it a number. This inconsistency is frustrating and I don't know how to correct it. Yes, I could use a double apostrophe, but there are 100 other people that use this and I don't want to revise instructions for all of them.

Is there a way in the SumIf formula I can include an IsNonText so as to verify it is a number before including it in numbers to be summed? Any other ideas?

Thanks very much!

 

[StephenCrump]

This sounds like a recipe for disaster ... relying on 100+ people to correctly insert apostrophes?

If you're tagging projects as completed, why not simply expand your SUMIF to SUMIFS, and check both the year, and the status as not completed?

 

[hcgood]

This sounds like a recipe for disaster ... relying on 100+ people to correctly insert apostrophes?

If you're tagging projects as completed, why not simply expand your SUMIF to SUMIFS, and check both the year, and the status as not completed?

Some projects begin their Phase 2 begin in the middle of a fiscal year, e.g. 2024. To show if a stage was completed in the previous Phase, 2024 Q1 (Oct-Dec 2023) we use the apostrophe in front of the 2024. Conditional Formatting turns the cell gray because it sees it as Text.

But the SumIf sees it as a number whereas the Conditional Formatting sees it as Text. Go figure?!

To answer your question, we're only tagging Stages as completed using '2024 to show it as completed in FY23 and 2024 (without apostrophe) to show this Stage as part of the current Plan still to be completed.

If SumIf would see the '2024 as a number there'd be no problem. I don't think there is a way with either SUMIF or SUMIFS criteria to check if a cell is text. No need for that, but in this case it would be helpful due to Excel's seeming inconsistency.

 

[Alex Blakenburg]

I agree with @StephenCrump that is very a risky approach, why not use a letter or word identifier as opposed to just a text year.

If you abolutely must go ahead either of these should work.
Please update your account profile to show the version of excel you are using:

Book1
	A	B	C	D	E
1	Year	Amt	Filter	SumProduct	Criteria
2	2022	10	100	100	2022
3	2022	100
Sheet1

Cell Formulas
Range	Formula
C2	C2	=FILTER(B2:B9,A2:A9=E2)
D2	D2	=SUMPRODUCT((A2:A9=E2)*(B2:B9))

 

[shift-del]

Hello Stephen

I suggest to insert a separate "status" column.

SUMIF() and its relatives always try a type conversion (text to number).

The Countif bug (and how to avoid it)

The Countif function counts the number of cells in a range that match some criterion. If we enter 1,2,3 in cells A1:A3 and enter =COUNTIF($A$1:$A$3,A1) somewhere else, it will return 1: But if we …

newtonexcelbach.com

 

[hcgood]

Hi. In my spreadsheet designed to handle a project plan, if a stage was completed in the previous phase we type an apostrophe in front of the year, e.g. '2022 so it doesn't affect calculations for the current project, but still retains the historical information. In a nearby cell if I type the "IsText" function on this cell it correctly shows as True because of the apostrophe.

However, if I include this '2022 column in a SumIf formula and the criteria is 2022 there is a problem. It matches the '2022 with the 2022, thinks the '2022 is a number, sums as part of the total and throws off my calculations. Only if I enter a second apostrophe in front of the '2022 (appearing as ''2022) does it now recognize it as text and calculates correctly because it now sees it as text.

I don't understand why in one usage it sees it as text with only one apostrophe and works correctly, but with the SumIf it considers it a number. This inconsistency is frustrating and I don't know how to correct it. Yes, I could use a double apostrophe, but there are 100 other people that use this and I don't want to revise instructions for all of them.

Is there a way in the SumIf formula I can include an IsNonText so as to verify it is a number before including it in numbers to be summed? Any other ideas?

Thanks very much!

I think I found the solution using SUMIFS with the second criteria to determine if it is text using the wildcard "<>*". So far seems to work.
Thanks Stephen for your help.

 

[RoryA]

However, if I include this '2022 column in a SumIf formula and the criteria is 2022 there is a problem. It matches the '2022 with the 2022, thinks the '2022 is a number, sums as part of the total and throws off my calculations.

To clarify, I presume you aren't saying that it is adding the number 2022 to your total, because that should not happen. SUMIF would match both the number 2022 and the text "2022" to a criterion of 2022 so it would include any number in the sum range for that row, which I assume is what you mean?