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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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,130,212
Messages
5,640,891
Members
417,177
Latest member
njosh

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