Sumif combined with value and right two formulas

[Pilara]

Hi,

I need to write two formulas, for the first one my data looks like below:

A B
1 Text Text
2 01.02.2021 03.03.2021
3
4 Text Text
5 12.12.2020 03.02.2021

There are about 20 - 50 rows, some of them are empty, all data is formated as General, but in some rows i have date (also formated as General).
Formula i have to write has to check if in rows where is date year is the same in column A and B, i have tried using sumif combined with value and right function to sum all the years in both columns and then compare totals with if, but I did not succeed, i also tried sumproduct function, but I am not sure how this function works and i did not succeed as well.

For the second formula my data set looks like below:

A B
1 153,64 xxxxxxx110
2 20,10 xxxxxxx213
3 11,11 xxxxxxx570
4 45,45 xxxxxxx841
5 123,23- xxxxxxx579

I need to sum all values in colmun A only if in column B end of string is 213 or 841. Just to make it more funny data in column A is formated as General and data in colum B is formated as Number, I have also 20-50 rows of data

Both formulas have to be written in only one cell without and helper columns.

Can anyone help me?

 

[Michael M]

CAn you use Xl2BB to post samples of your data please.....see my tag for using the XL2BB function

 

[jtakw]

Hi,

And also please clarify,
For your first set, are your Dates in the two columns real date values, or are they Text values, or mixed?
For your 2nd set, basically the same question, are the numbers in the 2 columns Real numbers or Text, or mixed?

 

[Pilara]

Sorry, I am not allowed to install any Add-Ins on this computer, please find screenshot with sample data for the first formula below

And for the second formula

I hope it helps

 

[Pilara]

Hi,

And also please clarify,
For your first set, are your Dates in the two columns real date values, or are they Text values, or mixed?
For your 2nd set, basically the same question, are the numbers in the 2 columns Real numbers or Text, or mixed?

For the first set all data is formated as General, there are no date value or mixed values
For the second set all data in column A is formated as General and in column B as Number, but the first character in column B is a letter.
There are no mixed values in the same column, only Number or General

 

[Michael M]

Can you upload a sample to dropbox then ? You data is a contradiction, if column B is a number, it can't have a letter in it.
AND post ing with "xxxxxx" as a substitute doesn't help us, help you !!

 

[jtakw]

I see that in your 2nd set, Column A, you have Text/Numbers that ends with a "Hyphen/Dash", it that Actually in the cell, or it's there by Custom Format?

Assuming that the "-" is Actually in the cell, try these, adjust Cell/Range references to suit you data:

Book3.xlsx
	A	B	C
1	Doc.Date	PostDate	2
2	01.02.2021	03.03.2021
3
4	Doc.Date	PostDate
5	12.12.2020	03.02.2021
6
7
8	Doc.Date	PostDate
9	04.03.2021	19.03.2021
10
11
12
13
14
15	153.64	xxxx06110	306.8
16	163.22	xxxx10213
17	155.37-	xxxx19570
18	143.58-	xxxx28841
19	177.04-	xxxx07570
20	153.64-	xxxx06579
21
22
23
24
Sheet855

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT((RIGHT(A1:A12,4)<>"Date")*(A1:A12<>"")*(RIGHT(A1:A12,4)=RIGHT(B1:B12,4)))
C15	C15	=SUMPRODUCT((SUBSTITUTE(A15:A30&0,"-",""))*((RIGHT(B15:B30,3)="213")+(RIGHT(B15:B30,3)="841")))

 

[Pilara]

I see that in your 2nd set, Column A, you have Text/Numbers that ends with a "Hyphen/Dash", it that Actually in the cell, or it's there by Custom Format?

Assuming that the "-" is Actually in the cell, try these, adjust Cell/Range references to suit you data:

Book3.xlsx
	A	B	C
1	Doc.Date	PostDate	2
2	01.02.2021	03.03.2021
3
4	Doc.Date	PostDate
5	12.12.2020	03.02.2021
6
7
8	Doc.Date	PostDate
9	04.03.2021	19.03.2021
10
11
12
13
14
15	153.64	xxxx06110	306.8
16	163.22	xxxx10213
17	155.37-	xxxx19570
18	143.58-	xxxx28841
19	177.04-	xxxx07570
20	153.64-	xxxx06579
21
22
23
24
Sheet855

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT((RIGHT(A1:A12,4)<>"Date")*(A1:A12<>"")*(RIGHT(A1:A12,4)=RIGHT(B1:B12,4)))
C15	C15	=SUMPRODUCT((SUBSTITUTE(A15:A30&0,"-",""))*((RIGHT(B15:B30,3)="213")+(RIGHT(B15:B30,3)="841")))

Thank You jtakw, that is much closer to what i got, but it is still not the solution i need, i tried to adjust those formulas, but i failed.
The perfect solution for me would be in both examples when formulas return "check" or "ok".
The first formula should return "ok", when every year in column A is the same as in column B, in my example it should return "check", but if we change year in cell A5 to 12.12.2021 it should return "ok"
The second formula should return "check" if any 3 last digits from column B is not 213 or 841, in my example it should return "check", but if we change last 3 digits in column B rows 15, 17, 19, 20 to 213 or 841 it should return "ok".
Once again thank You very much for help, I really appreciate that!

 

[jtakw]

What you're describing now is completely different than your OP.
In OP,
For 1st set, you wanted to check if Year in A and B are the same.
For 2nd set, you wanted to SUM column A if B ended in 213 or 841.

I'll get back to you later.

 

[jtakw]

Use these for your New requirements:

Book3.xlsx
	A	B	C
1	Doc.Date	PostDate	Check
2	01.02.2021	03.03.2021
3
4	Doc.Date	PostDate
5	12.12.2020	03.02.2021
6
7
8	Doc.Date	PostDate
9	04.03.2021	19.03.2021
10
11
12
13
14
15	153.64	xxxx06110	Check
16	163.22	xxxx10213
17	155.37-	xxxx19570
18	143.58-	xxxx28841
19	177.04-	xxxx07570
20	153.64-	xxxx06579
21
22
Sheet855

Cell Formulas
Range	Formula
C1	C1	=IF(SUMPRODUCT((RIGHT(A1:A12,4)<>"Date")*(A1:A12<>"")*(RIGHT(A1:A12,4)<>RIGHT(B1:B12,4))),"Check","Ok")
C15	C15	=IF(SUMPRODUCT((B15:B30<>"")*(RIGHT(B15:B30,3)<>"213")*(RIGHT(B15:B30,3)<>"841")),"Check","Ok")