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?
 

Pilara

New Member
Joined
Mar 22, 2021
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
That is perfect, thank You so much!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,536
Messages
5,636,890
Members
416,947
Latest member
asher_nk

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