# Sumif combined with value and right two formulas

#### Pilara

##### New Member
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?

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### Michael M

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

#### jtakw

##### Well-known Member
Hi,

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

##### New Member
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

##### New Member

Hi,

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

##### Well-known Member
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

##### Well-known Member

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
ABC
1Doc.DatePostDate2
201.02.202103.03.2021
3
4Doc.DatePostDate
512.12.202003.02.2021
6
7
8Doc.DatePostDate
904.03.202119.03.2021
10
11
12
13
14
15153.64xxxx06110306.8
16163.22xxxx10213
17155.37-xxxx19570
18143.58-xxxx28841
19177.04-xxxx07570
20153.64-xxxx06579
21
22
23
24
Sheet855
Cell Formulas
RangeFormula
C1C1=SUMPRODUCT((RIGHT(A1:A12,4)<>"Date")*(A1:A12<>"")*(RIGHT(A1:A12,4)=RIGHT(B1:B12,4)))
C15C15=SUMPRODUCT((SUBSTITUTE(A15:A30&0,"-",""))*((RIGHT(B15:B30,3)="213")+(RIGHT(B15:B30,3)="841")))

#### Pilara

##### New Member
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
ABC
1Doc.DatePostDate2
201.02.202103.03.2021
3
4Doc.DatePostDate
512.12.202003.02.2021
6
7
8Doc.DatePostDate
904.03.202119.03.2021
10
11
12
13
14
15153.64xxxx06110306.8
16163.22xxxx10213
17155.37-xxxx19570
18143.58-xxxx28841
19177.04-xxxx07570
20153.64-xxxx06579
21
22
23
24
Sheet855
Cell Formulas
RangeFormula
C1C1=SUMPRODUCT((RIGHT(A1:A12,4)<>"Date")*(A1:A12<>"")*(RIGHT(A1:A12,4)=RIGHT(B1:B12,4)))
C15C15=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

##### Well-known Member
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

##### Well-known Member
Use these for your New requirements:

Book3.xlsx
ABC
1Doc.DatePostDateCheck
201.02.202103.03.2021
3
4Doc.DatePostDate
512.12.202003.02.2021
6
7
8Doc.DatePostDate
904.03.202119.03.2021
10
11
12
13
14
15153.64xxxx06110Check
16163.22xxxx10213
17155.37-xxxx19570
18143.58-xxxx28841
19177.04-xxxx07570
20153.64-xxxx06579
21
22
Sheet855
Cell Formulas
RangeFormula
C1C1=IF(SUMPRODUCT((RIGHT(A1:A12,4)<>"Date")*(A1:A12<>"")*(RIGHT(A1:A12,4)<>RIGHT(B1:B12,4))),"Check","Ok")
C15C15=IF(SUMPRODUCT((B15:B30<>"")*(RIGHT(B15:B30,3)<>"213")*(RIGHT(B15:B30,3)<>"841")),"Check","Ok")

Replies
6
Views
93
Replies
4
Views
98
Replies
5
Views
95
Replies
26
Views
358
Replies
4
Views
168

1,130,295
Messages
5,641,401
Members
417,208
Latest member
wendy823

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back