SUMIF for horizontal column

[chongjunn]

Dear All

I want to sumif the Quantity supplied for 2019 (in Row J) and 2020 (in Row K) for each companies (A,B,C) based on the data from Row B-I.

How do i do that?

Column	B	C	D	E	F	G	H	I	J	K
	DATE	QTY SUPPLIED	DATE	QTY SUPPLIED	DATE	QTY SUPPLIED	DATE	QTY SUPPLIED	QTY SUPPLIED IN 2019	QTY SUPPLIED IN 2020
Company A	9/12/2019​	72​	16/12/2019​	144​	17/12/2019​	24​	6/1/2020​	80​
Company B	31/10/2019​	120​	2/1/2020​	80​
Company C	19/12/2019​	2​	31/1/2020​	12​	20/4/2020​	6​	29/5/2020​	12​

 

[jasonb75]

SUMIF for horizontal column

There is no such thing as a horizontal column, see link below

Difference Between Rows and Columns (with Comparison Chart) - Key Differences

The main difference between rows and columns are discussed in this article. Row is an order in which people, objects or figures are placed alongside or in a straight line. A vertical division of facts, figures or any other details based on category, is called column.

keydifferences.com

In regard to your question, I think that you want something like the formula below in J2

=SUMIFS($C2:$I2,$B1:$H1,"DATE",$B2:$H2,">=01/01/2019",$B2:$H2,"<=31/12/2019")

Your data is not formatted in a way that will work well with summaries or ananlysis.

 

[chongjunn]

Dear jasonb75

Thank you for your reply. So there is no way to do that unless you reformat the dataset?

 

[shaowu459]

MrExcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		DATE	QTY SUPPLIED	DATE	QTY SUPPLIED	DATE	QTY SUPPLIED	DATE	QTY SUPPLIED	QTY SUPPLIED IN 2019	QTY SUPPLIED IN 2020
2	Company A	2019/12/9	72	2019/12/16	144	2019/12/17	24	2020/1/6	80	240	80
3	Company B	2019/10/31	120	2020/1/2	80					120	80
4	Company C	2019/12/19	2	2020/1/31	12	2020/4/20	6	2020/5/29	12	2	30
Sheet8

Cell Formulas
Range	Formula
J2:J4	J2	=SUM(SUMIFS(C2:I2,B2:H2,">=2019-1-1",B2:H2,"<=2019-12-31"))
K2:K4	K2	=SUM(SUMIFS(C2:I2,B2:H2,">=2020-1-1",B2:H2,"<=2020-12-31"))

 

[shaowu459]

MrExcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		DATE	QTY SUPPLIED	DATE	QTY SUPPLIED	DATE	QTY SUPPLIED	DATE	QTY SUPPLIED	QTY SUPPLIED IN 2019	QTY SUPPLIED IN 2020
2	Company A	2019/12/9	72	2019/12/16	144	2019/12/17	24	2020/1/6	80	240	80
3	Company B	2019/10/31	120	2020/1/2	80					120	80
4	Company C	2019/12/19	2	2020/1/31	12	2020/4/20	6	2020/5/29	12	2	30
Sheet8

Cell Formulas
Range	Formula
J2:J4	J2	=SUMPRODUCT((YEAR(B2:H2)=2019)*C2:I2*MOD(COLUMN(A1:G1),2))
K2:K4	K2	=SUMPRODUCT((YEAR(B2:H2)=2020)*C2:I2*MOD(COLUMN(A1:G1),2))

 

[shaowu459]

Sorry, I didn't see jasonb75's formula and suggestions in post #2. If you can reorganise your data, it will be easy to summarize or calculate using simple formulas and Pivot Table.

 

[chongjunn]

Dear Shaowu459

wow, it really do works! Thanks so much for your help sir