COUNTIF with a Multiplier Variation

[Unlucky-Phase]

Hello!

I'm wondering if it's possible to add a multiplier to COUNTIFS. I have a list of items, with a quantity, that are only purchased on certain weeks. I need to only count if it's checked on that week, and then when it is, multiple by the number in that column. I can do the COUNTIF okay but not sure on the multiplier.

This is my formula =COUNTIFS(C3:C7, "YES", A3:A7, "Apple")

C being the dates, A being the Item and B is the quantity. How do I get the COUNTIF to multiple the number returned ?

Thanks

 

[kweaver]

Do you sum the multiplier if the product is more than once?

Book2
	A	B	C	D	E	F
3	Apple		Yes	10		60
4	Banana		Yes	20
5	Orange		No	30
6	Pear		No	40
7	Apple		Yes	50
Sheet3

Cell Formulas
Range	Formula
F3	F3	=SUMPRODUCT((D3:D7)*(C3:C7="YES")*(A3:A7="Apple"))

\

An example or two using XL2BB would be helpful.

 

[Toadstool]

Hi MollyPJ,

I'm not following exactly what is multiplied. Is it really a total of quantity?

MollyPJ.xlsx
	A	B	C	D	E	F
1					Count	Total Qty
2	Fruit	Qty	Checked?		2	5
3	Apple	2	YES
4	Apple	4	NO
5	Orange	6	NO
6	Banana	8	NO
7	Apple	3	YES
Sheet1

Cell Formulas
Range	Formula
E2	E2	=COUNTIFS(C3:C7, "YES", A3:A7, "Apple")
F2	F2	=SUMIFS(B3:B7,C3:C7, "YES", A3:A7, "Apple")

 

[Unlucky-Phase]

This is what I'm attempting. Yes I guess I would need it to sum even if it was 1 - it wouldn't make a difference. Week 1 for Apple is currently 2 as it's counting how much it's appeared in that week but I need it to be 5 and then week 2 would be 8 not 3. If that makes sense

 

[kweaver]

Does this help?

Book2
	A	B	C	D	E	F	G
1	Item	Qty	Week1	Week2		Week1	Week2
2			9/2/2020	9/9/2020		Apple	Apple
3	Apple	1	YES	YES		5	8
4	Apple	4	YES	YES
5	Apple	3	No	YES
6	Banana	3	YES	YES
7	Pear	1		YES
Sheet3

Cell Formulas
Range	Formula
F3:G3	F3	=SUMPRODUCT((F2=$A$3:$A$7)*($B$3:$B$7)*("YES"=C$3:C$7))

 

[kweaver]

You can change F1 and F2 in this example.

Book2
	A	B	C	D	E	F
1	Item	Qty	Week1	Week2		Week2
2			9/2/2020	9/9/2020		Apple
3	Apple	1	YES	YES		8
4	Apple	4	YES	YES
5	Apple	3	No	YES
6	Banana	3	YES	YES
7	Pear	1		YES
Sheet3

Cell Formulas
Range	Formula
F3	F3	=SUMPRODUCT((F2=$A$2:$A$7)*($B$2:$B$7)*("YES"=INDEX(A2:D7,0,MATCH(F1,$A$1:$D$1,0))))

 

[Unlucky-Phase]

Thank-you! This worked for me kweaver