Calculation formula

fireslguk

Active Member
Joined
Nov 11, 2005
Messages
282
Hi all I have 3 columns

Column A - Dates in chronological order
Column B - Various Colours
Column c - A Cost

example data

01/11/2020 Red $23
04/12/2020 Green $4
31/12/2020 Blue $44
01/01/2021 Red $5
03/01/2021 White $8


I would like to specify a date range in the formula of a start and finish date choose a colour and the result of the formula is the sum

example

start date 01/11/2020 end date 01/01/2021 colour Red result = $28


what's the best way this is achieved ?
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
Hi, you should take a look at the SUMIFS() formula. For example:

Book11
ABCDEF
101/11/2020Red23Start01/11/2020
204/12/2020Green4End01/01/2021
331/12/2020Blue44ColourRed
401/01/2021Red5Formula28
503/01/2021White8
Sheet1
Cell Formulas
RangeFormula
F4F4=SUMIFS(C:C,A:A,">="&F1,A:A,"<="&F2,B:B,F3)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Another option IF you have Excel 365**

21 01 08.xlsm
ABCDEF
11/11/2020Red23Start1/11/2020
24/12/2020Green4End1/01/2021
331/12/2020Blue44ColourRed
41/01/2021Red5Formula28
53/01/2021White8
Sum
Cell Formulas
RangeFormula
F4F4=SUM(FILTER(C1:C5,(A1:A5>=F1)*(A1:A5<=F2)*(B1:B5=F3),0))


** I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also a good idea to investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
 
Last edited:

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
Option

Book1
ABCDEF
111/01/2020Red23Start11/01/2020
212/04/2020Green4End01/01/2021
331/12/2020Blue44ColourRed
401/01/2021Red5Formula28
501/03/2021White8
Sheet1
Cell Formulas
RangeFormula
F4F4=SUMPRODUCT($C$1:$C$5*($B$1:$B$5=$F$3)*($A$1:$A$5>=$F$1)*($A$1:$A$5<=$F$2))



Need Ctrl+Shift+Enter
Book1
ABCDEF
111/01/2020Red23Start11/01/2020
212/04/2020Green4End01/01/2021
331/12/2020Blue44ColourRed
401/01/2021Red5Formula28
501/03/2021White8
Sheet1
Cell Formulas
RangeFormula
F4F4=SUM($C$1:$C$5*($B$1:$B$5=$F$3)*($A$1:$A$5>=$F$1)*($A$1:$A$5<=$F$2))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,437
Messages
5,596,130
Members
414,043
Latest member
thomas Stein

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