Sumif combined with value and right two formulas

Pilara

New Member
Joined
Mar 22, 2021
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Oct 27, 2005
Messages
20,200
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
CAn you use Xl2BB to post samples of your data please.....see my tag for using the XL2BB function
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Mar 22, 2021
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Sorry, I am not allowed to install any Add-Ins on this computer, please find screenshot with sample data for the first formula below
1616456204300.png


And for the second formula

1616456517694.png


I hope it helps
 

Pilara

New Member
Joined
Mar 22, 2021
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
20,200
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 22, 2021
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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")
 
Solution

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top