Ask Your Question
0

Why can I not sum() the third column. Using nested if(and()) to test date column and a name coloumn which returns the correctly assigned 1 or 2.

asked 2018-01-24 22:41:31 +0200

pleurastic gravatar image

D E F 06/30/2018 I 1 06/30/2018 I 1 06/30/2018 C 2 06/30/2018 I 1 06/30/2018 I 1

Third column formula =IF(AND(YEAR(D23)=2018,E23="I"),"1",)&IF(AND(YEAR(D23)=2018,E23="C"),"2",)&IF(AND(YEAR(D23)=2018,E23="H"),"1",)&IF(AND(YEAR(D23)=2018,E23="Life"),"1",) I'm a rookie so be gentle

edit retag flag offensive close merge delete

Comments

Do you want sum or concatenate?

m.a.riosv gravatar imagem.a.riosv ( 2018-01-24 23:53:58 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-01-25 10:17:16 +0200

JohnSUN gravatar image

updated 2018-01-25 10:18:45 +0200

if you will create small auxiliary value table like this

small auxiliary value table

then your formula will be much easier

=(YEAR(D23)=2018)*IFERROR(VLOOKUP(E23, $M$2:$N$5, 2, 0), 0)
edit flag offensive delete link more

Comments

Thank you very much! It works perfectly. Solved.

pleurastic gravatar imagepleurastic ( 2018-01-26 02:06:28 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-01-24 22:41:31 +0200

Seen: 29 times

Last updated: Jan 25 '18