# Only include some values in column calculations

#### pnr8uk

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

many thanks

Paul

#### Attachments

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

### 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
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)

#### pnr8uk

##### New Member
Wow thank you so much - works a treat

Replies
3
Views
160
Replies
3
Views
108
Replies
1
Views
356
Replies
0
Views
129
Replies
16
Views
353

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?

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