Forum Discussion
How can I use content of several columns to create an outcome?
- Say in colum A I will put dates
- in column B I will put a description so text
- in column C I will type a number.
So lets say: If column A is a date in januari and column B contains the text 'loon' I want to display the number which is in column C in column D
If the text is anything else than 'loon' I dont want the number shown in column D
The formula that I posted is for a single cell only. For multiple cells:
ALS((MAAND(Kosten!A2:A60)=1)*(Kosten!B2:B60="loon"); Kosten!g2:g60; "")
If you'd like the sum in a single formula:
=SOMPRODUCT((MAAND(Kosten!A2:A60)=1)*(Kosten!B2:B60="loon")*Kosten!G2:G60)
3 Replies
Is the month January relevant?
If so, in D2:
=IF(AND(MONTH(A2)=1, B2="loon"), C2, "")
Otherwise, in D2:
=IF(B2="loon", C2, "")
Fill or copy downwards.
- Yes the month is relevant.
What I've got now, but doesnt work:
IF(AND(MONTH(Kosten!A2:A60)=1, Kosten!B2:B60="loon"), Kosten!g2:g60, "")
Based on my previous formula which did work 😉
=SOMPRODUCT((MAAND(Kosten!A2:A60)=1)*(Kosten!G2:G60))
So I want to combine the working formula with the value "loon" to retrieve only that ammount instead of all the january ammounts.The formula that I posted is for a single cell only. For multiple cells:
ALS((MAAND(Kosten!A2:A60)=1)*(Kosten!B2:B60="loon"); Kosten!g2:g60; "")
If you'd like the sum in a single formula:
=SOMPRODUCT((MAAND(Kosten!A2:A60)=1)*(Kosten!B2:B60="loon")*Kosten!G2:G60)
