Sum if the cells are not blank for specific products

[Goddess]

Hi, I need to sum if only both cells (B & C) are not blank. Some rows are totally blank, as we need to export the data from a system, thus there will be some weird formatting. Example as below

Sheet1:

In the example above, I need the result to return in another sheet as below:

Sheet 2:

Basically, only sum B & C if both columns have value in them (even if it's 0), and only for specific products as indicated in Sheet 2. Sheet 1 may have other products which I do not need.

Thanks!
Irene

 

[Habtest]

Hi, try:

Book1
	A	B	C	D	E	F
1	Apple	123	0		Apple	123
2					Orange	1011
3
4	Apple	233
5	Apple	234
6	Apple	4455
7	Apple	333
8
9	Orange	345	666
10
11	Orange	456
12	Orange	333
13	Orange	33
Sheet1

Cell Formulas
Range	Formula
F1:F2	F1	=SUMPRODUCT((Sheet1!$A$1:$A$13=E1)*(LEN(Sheet1!$B$1:$B$13)>0)*(LEN(Sheet1!$C$1:$C$13)>0)*(Sheet1!$B$1:$B$13+Sheet1!$C$1:$C$13))

 

[Alex Blakenburg]

Or this

20210513 Sumifs Simple.xlsx
	A	B
1	Apple	123
2	Orange	1011
Sheet2

Cell Formulas
Range	Formula
B1:B2	B1	=SUMPRODUCT((Sheet1!$A$1:$A$13=A1)*(Sheet1!$B$1:$B$13<>"")*(Sheet1!$C$1:$C$13<>"")*Sheet1!$B$1:$C$13)

 

[Goddess]

thanks! but not sure why it's not working on my sheet.. result is #Value! hmmm....

 

[Goddess]

Hi,

My formula is more like:

=SUMPRODUCT((Sheet1!$A:$A=$A1)*(Sheet1!$B:$B<>"")*(Sheet1!$C:$C<>"")*Sheet1!$B:$C)
or

=SUMPRODUCT((Sheet1!$A:$A=E1)*(LEN(Sheet1!$B:$B)>0)*(LEN(Sheet1!$C:$C)>0)*(Sheet1!$B:$B+Sheet1!$C:$C))

Is this the reason why it's not working? Btw, between B and C, there's a column. Some with text, most are blanks. So technically, I need to add B & D if both have values, even if it's 0.

Thanks!

 

[Goddess]

ok.. i think i found out the issue here.. there are headers (or titles) in my sheet, as it's exported from a system. Sample as below:

It's repeated multiple times in my entire sheet. Anyway to work around it?

Thanks!

 

[Alex Blakenburg]

ok.. i think i found out the issue here.. there are headers (or titles) in my sheet, as it's exported from a system. Sample as below:


View attachment 38700
It's repeated multiple times in my entire sheet. Anyway to work around it?

Thanks!

That image is quite different to the previous one

Can you please give us a sample of the data you want us to use as a basis ?
Ideally in XLBB Format, and if that is not possible Text Table but then advise if it starts in column A or not.
Also if the Sheet name is not Sheet1 what it is, so that you can copy in any formula that someone comes up with.

 

[Goddess]

Hi! Sorry for the confusion. . I didn't realise the headers would cause this issue.

I can't seem to install XL2BB... Below is what Sheet 1 looks like when I export from the system. Basically, the headers (comprise of 2 rows if you notice in column J) is repeated randomly throughout the sheet (more than 1K rows). I only need to sum up H & J if both columns have values in them, even if it's 0, for certain products.

In the example below, I need the result in Sheet 2:

Apple : H6 + J6 = 123
Orange : H23 + J23 = 738
I don't need the result of Pear

A

B

C

D

E

F

G

H

I

J

K

L

1​

Product

Description

Number

Order #

Start Date

Order Qty

Outstanding Qty

Available

Shortage Qty

OTW

IQC

Total

2​

(LS0+CT90+VT5)

3​

4​

5​

6​

Apple

FS123

435​

1001​

01.02.2021

4.00​

2.00​

123.00​

0.00​

0​

3,000.00​

7​

500.00​

8​

20,000.00​

9​

Apple

FS123

453​

1002​

02.02.2021

9.00​

4.00​

234.00​

10​

Apple

FS123

555​

1003​

05.02.2021

4.00​

2.00​

232.00​

11​

Apple

FS123

343​

1004​

06.02.2021

3.00​

0.00​

533.00​

12​

Apple

FS123

435​

1005​

10.03.2021

6.00​

5.00​

333.00​

13​

Apple

FS123

654​

1006​

11.03.2021

8.00​

0.00​

232.00​

14​

Apple

FS123

343​

1007​

30.03.2021

5.00​

0.00​

543.00​

15​

Apple

FS123

555​

1008​

15.04.2021

12.00​

6.00​

565.00​

16​

Apple

FS123

654​

1009​

17.05.2021

33.00​

21.00​

444.00​

17​

18​

Part Name

Description

Number

Order #

Start Date

Order Qty

Outstanding Qty

Available

Shortage Qty

OTW

IQC

Total

19​

(LS0+CT90+VT5)

20​

Apple

FS123

654​

1010​

06.02.2021

2.00​

1.00​

765.00​

21​

Apple

FS123

344​

1011​

10.03.2021

3.00​

2.00​

443.00​

22​

23​

Orange

FS345

987​

1001​

11.03.2021

5.00​

0.00​

344.00​

394.00​

0​

700​

24​

3000​

25​

26​

Orange

FS345

789​

1003​

30.03.2021

4.00​

0.00​

334.00​

27​

Orange

FS345

987​

1005​

15.04.2021

66.00​

60.00​

-6.00​

28​

Orange

FS345

666​

1006​

17.05.2021

54.00​

45.00​

-9.00​

29​

0.00​

30​

Pear

FS567

567​

1004​

07.06.2021

4.00​

2.00​

-2.00​

31​

32​

33​

Pear

FS567

765​

1006​

07.06.2021

7.00​

3.00​

-4.00​

34​

Pear

FS567

765​

1008​

14.06.2021

10.00​

5.00​

-5.00​

35​

Pear

FS567

456​

1010​

14.06.2021

9.00​

0.00​

-9.00​

36​

Pear

FS567

455​

1011​

14.06.2021

8.00​

5.00​

-3.00​

Thanks!

 

[Alex Blakenburg]

SumProduct does not like the text sprinkled through the Amount columns, SumIfs is a bit more forgiving.

See if this works for you.
It would have been handy to have the sheet name, I have used a sheet name of Data in my formula.

20210513 Sumifs Simple.xlsx
	A	B	C
1	Apple	344
2	Orange	30
3
Result

Cell Formulas
Range	Formula
B1:B2	B1	=SUMIFS(Data!$H$3:$H$37,Data!$A$3:$A$37,$A1,Data!$H$3:$H$37,"<>",Data!$J$3:$J$37,"<>")+ SUMIFS(Data!$J$3:$J$37,Data!$A$3:$A$37,$A1,Data!$H$3:$H$37,"<>",Data!$J$3:$J$37,"<>")

 

[Habtest]

Or this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Product	Description	Number	Order #	Start Date	Order Qty	Outstanding Qty	Available	Shortage Qty	OTW	IQC	Total		Apple	123
2										(LS0+CT90+VT5)				Orange	738
3
4
5
6	Apple	FS123	435	1001	01.02.2021	4.00	2.00	123.00		0.00	0	3,000.00
7												500.00
8												20,000.00
9	Apple	FS123	453	1002	02.02.2021	9.00	4.00	234.00
10	Apple	FS123	555	1003	05.02.2021	4.00	2.00	232.00
11	Apple	FS123	343	1004	06.02.2021	3.00	0.00	533.00
12	Apple	FS123	435	1005	10.03.2021	6.00	5.00	333.00
13	Apple	FS123	654	1006	11.03.2021	8.00	0.00	232.00
14	Apple	FS123	343	1007	30.03.2021	5.00	0.00	543.00
15	Apple	FS123	555	1008	15.04.2021	12.00	6.00	565.00
16	Apple	FS123	654	1009	17.05.2021	33.00	21.00	444.00
17
18	Part Name	Description	Number	Order #	Start Date	Order Qty	Outstanding Qty	Available	Shortage Qty	OTW	IQC	Total
19										(LS0+CT90+VT5)
20	Apple	FS123	654	1010	06.02.2021	2.00	1.00	765.00
21	Apple	FS123	344	1011	10.03.2021	3.00	2.00	443.00
22
23	Orange	FS345	987	1001	11.03.2021	5.00	0.00	344.00		394.00	0	700
24												3000
25
26	Orange	FS345	789	1003	30.03.2021	4.00	0.00	334.00
27	Orange	FS345	987	1005	15.04.2021	66.00	60.00		-6.00
28	Orange	FS345	666	1006	17.05.2021	54.00	45.00		-9.00
29									0.00
30	Pear	FS567	567	1004	07.06.2021	4.00	2.00		-2.00
31
32
33	Pear	FS567	765	1006	07.06.2021	7.00	3.00		-4.00
34	Pear	FS567	765	1008	14.06.2021	10.00	5.00		-5.00
35	Pear	FS567	456	1010	14.06.2021	9.00	0.00		-9.00
36	Pear	FS567	455	1011	14.06.2021	8.00	5.00		-3.00
Sheet1

Cell Formulas
Range	Formula
O1:O2	O1	=SUMPRODUCT((Sheet1!$A$1:$A$1999=N1)*(Sheet1!$H$1:$H$1999<>"")*(Sheet1!$J$1:$J$1999<>"")*IFERROR(Sheet1!$H$1:$H$1999+Sheet1!$J$1:$J$1999,0))