Calculation formula

[fireslguk]

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 ?

 

[FormR]

Hi, you should take a look at the SUMIFS() formula. For example:

Book11
	A	B	C	D	E	F
1	01/11/2020	Red	23		Start	01/11/2020
2	04/12/2020	Green	4		End	01/01/2021
3	31/12/2020	Blue	44		Colour	Red
4	01/01/2021	Red	5		Formula	28
5	03/01/2021	White	8
Sheet1

Cell Formulas
Range	Formula
F4	F4	=SUMIFS(C:C,A:A,">="&F1,A:A,"<="&F2,B:B,F3)

 

[Peter_SSs]

Another option IF you have Excel 365**

21 01 08.xlsm
	A	B	C	D	E	F
1	1/11/2020	Red	23		Start	1/11/2020
2	4/12/2020	Green	4		End	1/01/2021
3	31/12/2020	Blue	44		Colour	Red
4	1/01/2021	Red	5		Formula	28
5	3/01/2021	White	8
Sum

Cell Formulas
Range	Formula
F4	F4	=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.

 

[Dossfm0q]

Option

Book1
	A	B	C	D	E	F
1	11/01/2020	Red	23		Start	11/01/2020
2	12/04/2020	Green	4		End	01/01/2021
3	31/12/2020	Blue	44		Colour	Red
4	01/01/2021	Red	5		Formula	28
5	01/03/2021	White	8
Sheet1

Cell Formulas
Range	Formula
F4	F4	=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
	A	B	C	D	E	F
1	11/01/2020	Red	23		Start	11/01/2020
2	12/04/2020	Green	4		End	01/01/2021
3	31/12/2020	Blue	44		Colour	Red
4	01/01/2021	Red	5		Formula	28
5	01/03/2021	White	8
Sheet1

Cell Formulas
Range	Formula
F4	F4	=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.