Only include some values in column calculations

pnr8uk

New Member
Joined
Oct 6, 2018
Messages
19
Morning all

I was hoping someone could help me with the follow problem I have;

Below is the spreadsheet I am working on and the object of the calculation is to SUM column E and Column F based on the value(s) in Colum D The Name (Destination) and put the answer in L3 then L4 then L5 as the dates increase so the next row would be the 02/01/2021 etc.,

The value in L4 works fine it does indeed sum up all of the values if the text is Coral

However if I add £100 to Bet 365 the it goes onto the Coral overall value rather than to Bet 365 M(3)

This is the formula I've used in L3
=IF(VLOOKUP(L3,$D$4:$D$6000,1,TRUE)="Coral",SUMIF($A$4:$A$6000,K4,$E$4:$E$6000)-SUMIF($A4:$A6000,K4,$F$4:$F$6000),0)

and in M4

=IF(VLOOKUP(M3,$D$4:$D$6000,1,TRUE)="Bet 365",SUMIF($A$4:$A$6000,M4,$E$4:$E$6000)-SUMIF($A4:$A6000,M4,$F$4:$F$6000),0)

I realise the answer is correct according to the formula as the TRUE says if Coral Sum the column and Coral does exist in the Colum, however I want it to be selective, so if it's Coral do only coral values and if it's Bet 365 etc only do those values. There will be a lot more but for now just those two

Any ideas?


1605432478730.png



many thanks

Paul
 

Attachments

  • 1605432076266.png
    1605432076266.png
    28.5 KB · Views: 1
  • 1605432175417.png
    1605432175417.png
    42.6 KB · Views: 1

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

premjeet

New Member
Joined
Dec 4, 2014
Messages
16
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
You can use SUMIFS formula

Put this L4

=SUMIFS($E$4:$E$6000,$D$4:$D$6000,L3,$A$4:$A$6000,$K$4)-SUMIFS($F$4:$F$6000,$D$4:$D$6000,L3,$A$4:$A$6000,$K$4)

and in M4

=SUMIFS($E$4:$E$6000,$D$4:$D$6000,M3,$A$4:$A$6000,$K$4)-SUMIFS($F$4:$F$6000,$D$4:$D$6000,M3,$A$4:$A$6000,$K$4)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

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