SUMIF & MATCH

[Houscoogs]

I've tried to do a SUMIF and Match function in a cell and can't seem to get it to work. It appears that the SUMIF function is suming up a column and I wanted for it to sum based on a Cell that reflects column headers. For example, I want to perform a SUMIF function base on my month selection (January or February, etc.). Please advice.

 

[Brian from Maui]

This is just a guess based on your explanation,

=SUMPRODUCT((A1:L1=X1)*(A2:L100))

Where X1 houses a criteria, A1:L1 column headings.

 

[Houscoogs]

Thanks for the reply. What I wanted to do is to use the SUMIF function to sum from another spreadsheet but I don't want to type in the Column (i.e $F). I wanted to have the flexibility by typing in the match function to lookup based on the column name range.

 

[Aladin Akyurek]

Care to specify the ranges involved?

 

[Houscoogs]

here's an example:

Data Sheet:

Jan Feb Mar

A 5 6 4
B 7 2 9
A 8 4 7
C 6 5 5
B 7 4 2


Summary Sheet:

Select Month (B1): (Drop Down Box)

A: Return Data Based on===> "Sumif(A1:D7,A1,Match(B1,Month,0))


Expected Result based on select month: Jan = 13; Feb = 10; Mar = 11.

 

[Aladin Akyurek]

here's an example:

Data Sheet:

Jan Feb Mar

A 5 6 4
B 7 2 9
A 8 4 7
C 6 5 5
B 7 4 2


Summary Sheet:

Select Month (B1): (Drop Down Box)

A: Return Data Based on===> "Sumif(A1:D7,A1,Match(B1,Month,0))


Expected Result based on select month: Jan = 13; Feb = 10; Mar = 11.

A SumProduct formula would also the job, but not needed here...

Book7
	A	B	C	D	E	F	G	H	I
1		Jan	Feb	Mar				Jan	Mar
2	A	5	6	4			A	13	11
3	B	7	2	9			B	14	11
4	A	8	4	7
5	C	6	5	5
6	B	7	4	2
7
Sheet1

H2, copied across and down:

=SUMIF(Sheet1!$A$2:$A$6,$G2,INDEX(Sheet1!$B$2:$D$6,0,MATCH(H$1,Sheet1!$B$1:$D$1,0)))

 

[Houscoogs]

Thank you....it works great.