Combine SUMPRODUCT & SUMIF into one formula

[yeoman99]

Hi,

I have a dataset that I wish to analyse at summary level for a particular timeframe of the project. See below for example.

	Act	Act	Fcst	Act
	Jan	Feb	Mar	Accruals	Actuals to Date
Coffee	£20	£10	£5	£2	£32
Tea	£5	£8	£9	£5	£18
Juice	£7	£3	£12	£6	£16
Coffee	£12	£17	£25	£13	£42
				Coffee	£74

I have used a combination of SUMPRODUCT & SUMIF to total the Jan, Feb & Accrual columns as below.

=SUMPRODUCT($S$3:$AA$139*($A$3:$A$139=K14))+SUMIF($A$3:$A$139,K14,$BG$3:$BG$139)

It works but looks a little messy. As the accrual column in the actual sheet is away out to the right I'm wondering if there's a way to combine this into one distinct formula that gives me the coffee total without the need for the SUMIF part?

Thanks,
Neil

 

[ExceLoki]

what version of excel are you using?
you can add this to your profile so when you post others will know how to better help you.

 

[Dave Patton]

N.B. You can post a concise example of your challenge with the forum's tool named XL2BB.
An example will show what data is in the cells; I assumed actual numbers and not text.

The following may help. Try the example (paste it to a clean sheet) and then edit and try it with your data.

SumProduct2022.xlsm
	A	B	C	D	E	F
1
2		Act	Act	Fcst	Act
3		Jan	Feb	Mar	Accruals
4	Coffee	20	10	5	2
5	Tea	5	8	9	5
6	Juice	7	3	12	6
7	Coffee	12	17	25	13
8
9					Coffee	£74
10					Coffee	74
11						74
12
7a

Cell Formulas
Range	Formula
F10	F10	=SUMPRODUCT(--(A4:A7=E10)*(B4:B7+C4:C7+E4:E7))
F11	F11	=SUM((A4:A7=E10)*(B4:B7+C4:C7+E4:E7))

 

[ExceLoki]

here are another couple of ways that may fork for you:

Book1
	A	B	C	D	E	F	G
1		Act	Act	Fcst	Act
2		Jan	Feb	Mar	Accruals	Actuals to Date	Actuals to Date
3	Coffee	20	10	5	2	32	32
4	Tea	5	8	9	5	18	18
5	Juice	7	3	12	6	16	16
6	Coffee	12	17	25	13	42	42
7
8					Coffee	74	74
Sheet1

Cell Formulas
Range	Formula
F3:F6	F3	=SUMIF($B$1:$E$1,"Act",B3:E3)
G3:G6	G3	=SUM(FILTER(B3:E3,$B$1:$E$1="act"))
F8	F8	=SUMIF(A3:A6,E8,F3:F6)
G8	G8	=SUM(FILTER(FILTER(B3:E6,B1:E1="Act"),A3:A6=E8))

Cells with Data Validation
Cell	Allow	Criteria
E8	List	=$A$3:$A$5