SUMPRODUCT, with text

[vytsak]

Hi everyone,

i have function that counts based on week, year and another criteria.
=SUMPRODUCT(--(WEEKNUM(Table1[Date]+0,21)=[@Week]),--(YEAR(Table1[Date]+0)=[@Year]),--ISNUMBER(SEARCH("BBB",Table1[Items]))).

Is it possible to have this formula ignore rows that has text in column A, between the dates?
The formula currently returns #VALUE!.

Book1.xlsx
	A	B	C
1	Item	Date
2	BBB	25.01.2021
3	BBB	28.01.2021
4	BBB	07.02.2021
5	AAA	08.02.2021
6	BBB	NA
7	BBB	10.02.2021
8
9
10
11
12	Year	Week	Meas. Supports
13	2021	01	#VALUE!
14	2021	02	#VALUE!
15	2021	03	#VALUE!
16	2021	04	#VALUE!
17	2021	05	#VALUE!
18	2021	06	#VALUE!
19	2021	07	#VALUE!
Sheet1

Cell Formulas
Range	Formula
C13	C13	=SUMPRODUCT(--(WEEKNUM(Table2[Date]+0,21)=[@Week]),--(YEAR(Table2[Date]+0)=[@Year]),--ISNUMBER(SEARCH("BBB",Table2[Item])))
C14:C19	C14	=SUMPRODUCT(--(WEEKNUM($B$2:$B$7+0,21)=[@Week]),--(YEAR($B$2:$B$7+0)=[@Year]),--ISNUMBER(SEARCH("BBB",A2:A7)))

 

[maabadi]

Try this:

ExampleWorkbook.xlsx
	A	B	C	D
1	Item	Date
2	BBB	44221
3	BBB	44224
4	BBB	44234
5	AAA	44235
6	BBB	NA
7	BBB	44237
8
9
10
11
12	Year	Week	Meas. Supports
13	2021	1	0
14	2021	2	0
15	2021	3	0
16	2021	4	2
17	2021	5	1
18	2021	6	1
19	2021	7	0
20
Sheet1

Cell Formulas
Range	Formula
C13:C19	C13	=SUMPRODUCT(--(WEEKNUM(IF(ISNUMBER(Table2[Date]),Table2[Date],0)+0,21)=[@Week]),--(YEAR(IF(ISNUMBER(Table2[Date]),Table2[Date])+0)=[@Year]),--ISNUMBER(SEARCH("BBB",Table2[Item])),--ISNUMBER(Table2[Date]))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[charli chali]

Hey,

I have this fantastic sumproduct formula that works very well.
=SUMPRODUCT(0+ISTEXT(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",1,$B6:$B10,$B$12:$B$16,$B$18:$B$22),",","</b><b>")&"</b></a>","//b[not(preceding::*=.)]")))

However, I would like to add condition, that count only if the status in column c "tick" meaning yes. If not, don't count.

How do I do that?

sumproduct.xlsx
	A	B	C	D
2	List of Projects
3	Description of Indicators		Status YR1 (Oct 2017 -Sept 2018)	Total YR1 (Oct 2017 -Sept 2018)
4	Beans			15
5	List of Seed companies (Seed multiplier/Distributor)			4
6	1	Seed Company_1	✓	1
7	2	Seed Company_2	✓	1
8	3	Seed Company_3		0
9	4	Seed Company_4	✓	1
10	5	Seed Company_5	✓	1
11	List of Public Breeders (NARs, IARCs, and Universities)			4
12	1.	Public Breeder (NARs IARCs and Universities)__1	✓	1
13	2.	Public Breeder (NARs IARCs and Universities)__2	✓	1
14	3.	Public Breeder (NARs IARCs and Universities)__3		0
15	4.	Public Breeder (NARs IARCs and Universities)__4	✓	1
16	5.	Public Breeder (NARs IARCs and Universities)__5	✓	1
17	List of Others Institutions (e.g. NGOs)			4
18	1.	Others Institutions e.g. NGOs__1	✓	1
19	2.	Others Institutions e.g. NGOs__2	✓	1
20	3.	Others Institutions e.g. NGOs__3		0
21	4.	Others Institutions e.g. NGOs__4	✓	1
22	5.	Others Institutions e.g. NGOs__5	✓	1
23	Irish Potato
24	List of Seed companies (Seed multiplier/Distributor)			4
25	1	Seed Company_1	✓	1
26	2	Seed Company_2	✓	1
27	3	Seed Company_3		0
28	4	Seed Company_4	✓	1
29	5	Seed Company_5	✓	1
30	List of Public Breeders (NARs, IARCs, and Universities)			4
31	1.	Public Breeder (NARs IARCs and Universities)__1	✓	1
32	2.	Public Breeder (NARs IARCs and Universities)__2	✓	1
33	3.	Public Breeder (NARs IARCs and Universities)__3		0
34	4.	Public Breeder (NARs IARCs and Universities)__4	✓	1
35	5.	Public Breeder (NARs IARCs and Universities)__5	✓	1
36	List of Others Institutions (e.g. NGOs)			4
37	1.	Others Institutions e.g. NGOs__1	✓	1
38	2.	Others Institutions e.g. NGOs__2	✓	1
39	3.	Others Institutions e.g. NGOs__3		0
40	4.	Others Institutions e.g. NGOs__4	✓	1
41	5.	Others Institutions e.g. NGOs__5	✓	1
42	Sorghum
43	List of Seed companies (Seed multiplier/Distributor)			4
44	1	Seed Company_1	✓	1
45	2	Seed Company_2	✓	1
46	3	Seed Company_3		0
47	4	Seed Company_4	✓	1
48	5	Seed Company_5	✓	1
49	List of Public Breeders (NARs, IARCs, and Universities)			4
50	1.	Public Breeder (NARs IARCs and Universities)__1	✓	1
51	2.	Public Breeder (NARs IARCs and Universities)__2	✓	1
52	3.	Public Breeder (NARs IARCs and Universities)__3		0
53	4.	Public Breeder (NARs IARCs and Universities)__4	✓	1
54	5.	Public Breeder (NARs IARCs and Universities)__5	✓	1
55	List of Others Institutions (e.g. NGOs)			4
56	1.	Others Institutions e.g. NGOs__1	✓	1
57	2.	Others Institutions e.g. NGOs__2	✓	1
58	3.	Others Institutions e.g. NGOs__3		0
59	4.	Others Institutions e.g. NGOs__4	✓	1
60	5.	Others Institutions e.g. NGOs__5	✓	1
61	Irish Potato			7
62	List of Seed companies (Seed multiplier/Distributor)			4
63	1	Seed Company_1	✓	1
64	2	Seed Company_2	✓	1
65	3	Seed Company_3		0
66	4	Seed Company_4	✓	1
67	5	Seed Company_5	✓	1
68	List of Public Breeders (NARs, IARCs, and Universities)			4
69	1.	Public Breeder (NARs IARCs and Universities)__1	✓	1
70	2.	Public Breeder (NARs IARCs and Universities)__2	✓	1
71	3.	Public Breeder (NARs IARCs and Universities)__3		0
72	4.	Public Breeder (NARs IARCs and Universities)__4	✓	1
73	5.	Public Breeder (NARs IARCs and Universities)__5	✓	1
74	List of Others Institutions (e.g. NGOs)			4
75	1.	Others Institutions e.g. NGOs__1	✓	1
76	2.	Others Institutions e.g. NGOs__2	✓	1
77	3.	Others Institutions e.g. NGOs__3		0
78	4.	Others Institutions e.g. NGOs__4	✓	1
79	5.	Others Institutions e.g. NGOs__5	✓	1
Sheet1

Cell Formulas
Range	Formula
C78:C79,C75:C76,C72:C73,C69:C70,C66:C67,C63:C64,C59:C60,C56:C57,C53:C54,C50:C51,C47:C48,C44:C45,C40:C41,C37:C38,C34:C35,C31:C32,C28:C29,C25:C26,C21:C22,C18:C19,C15:C16,C12:C13,C9:C10,C6:C7	C6	=UNICHAR(10003)
D4	D4	=SUMPRODUCT(0+ISTEXT(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",1,$B6:$B10,$B$12:$B$16,$B$18:$B$22),",","</b><b>")&"</b></a>","//b[not(preceding::*=.)]")))
D5,D74,D68,D61:D62,D55,D49,D43,D36,D30,D24,D17,D11	D5	=SUM(D6:D10)
D75:D79,D69:D73,D63:D67,D56:D60,D50:D54,D44:D48,D37:D41,D31:D35,D25:D29,D18:D22,D12:D16,D6:D10	D6	=(COUNTIF(C6,UNICHAR(10003)))

 

[maabadi]

Welcome to MrExcel Message Board.
Please Start New Thread. Never don't ask your question at the threads with started with other members. this is rule &
Because new threads until don't have answers has very more visitors & your Problem more quicker can solve.