Ask Your Question
0

Compare a Date within a column range and output corresponding string in separate column

asked 2017-12-01 19:41:49 +0100

eeliottheking gravatar image

Hello. I used a SumIF function to do the same thing with a number (Since there is only one matching date per entry in the range it works even though nothing is being added.)

=SUMIF($Import.$A$4:$Import.$A$369,"="&A5,$Import.$AW$4:$Import.$AW$369)

(Checks If date value between Import.A4:A369 is the same as A5 on current page, and if it is outputs corresponding Value in Import.AW4:AW369)

how do I accomplish a similar thing but where all Values between Import.AW4:AW369 are strings?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-12-01 20:33:52 +0100

Lupp gravatar image

updated 2017-12-01 20:36:00 +0100

Of course you cannot sum (add) strings. If you are talking of strings that have a numeric format like "123" you can use the VALUE() function like VALUE("123")=123, or, resorting to automatic conversion 0+"123"=123. You will need to enter the expression for array-evaluation then.

If your strings are non-numeric text, I would suspect you try to redefine "add" or "sum" as concatenation. If so you need to use the CONCAT() function or the TEXTJOIN() function which requires a version >= 5.3.

If you again can assure that only one date will match, or if you can accept the first match, this is a case for VLOOKUP().

Also your working SUMIF formula should be replaced using VLOOKUP() or a combination of MATCH() with INDEX() or OFFSET().

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-12-01 19:41:49 +0100

Seen: 27 times

Last updated: Dec 01 '17