SUMIF for horizontal column

chongjunn

New Member
Joined
Jun 11, 2020
Messages
3
Office Version
  1. 2011
Platform
  1. Windows
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?

ColumnBCDEFGHIJK
DATEQTY SUPPLIEDDATEQTY SUPPLIEDDATEQTY SUPPLIEDDATEQTY SUPPLIEDQTY SUPPLIED IN 2019QTY 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​
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
SUMIF for horizontal column
There is no such thing as a horizontal column, see link below

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.
 
Upvote 0
MrExcel.xlsx
ABCDEFGHIJK
1DATEQTY SUPPLIEDDATEQTY SUPPLIEDDATEQTY SUPPLIEDDATEQTY SUPPLIEDQTY SUPPLIED IN 2019QTY SUPPLIED IN 2020
2Company A2019/12/9722019/12/16 144 2019/12/17 24 2020/1/68024080
3Company B2019/10/311202020/1/2 80 12080
4Company C2019/12/1922020/1/31 12 2020/4/20 6 2020/5/2912230
Sheet8
Cell Formulas
RangeFormula
J2:J4J2=SUM(SUMIFS(C2:I2,B2:H2,">=2019-1-1",B2:H2,"<=2019-12-31"))
K2:K4K2=SUM(SUMIFS(C2:I2,B2:H2,">=2020-1-1",B2:H2,"<=2020-12-31"))
 
Upvote 0
MrExcel.xlsx
ABCDEFGHIJK
1DATEQTY SUPPLIEDDATEQTY SUPPLIEDDATEQTY SUPPLIEDDATEQTY SUPPLIEDQTY SUPPLIED IN 2019QTY SUPPLIED IN 2020
2Company A2019/12/9722019/12/16 144 2019/12/17 24 2020/1/68024080
3Company B2019/10/311202020/1/2 80 12080
4Company C2019/12/1922020/1/31 12 2020/4/20 6 2020/5/2912230
Sheet8
Cell Formulas
RangeFormula
J2:J4J2=SUMPRODUCT((YEAR(B2:H2)=2019)*C2:I2*MOD(COLUMN(A1:G1),2))
K2:K4K2=SUMPRODUCT((YEAR(B2:H2)=2020)*C2:I2*MOD(COLUMN(A1:G1),2))
 
Upvote 0
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.
 
Last edited:
Upvote 0
Dear Shaowu459

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

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

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
Back
Top