COUNTIF with a Multiplier Variation

Unlucky-Phase

New Member
Joined
Sep 23, 2019
Messages
14
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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,448
Office Version
  1. 365
  2. 2010
Do you sum the multiplier if the product is more than once?

Book2
ABCDEF
3AppleYes1060
4BananaYes20
5OrangeNo30
6PearNo40
7AppleYes50
Sheet3
Cell Formulas
RangeFormula
F3F3=SUMPRODUCT((D3:D7)*(C3:C7="YES")*(A3:A7="Apple"))
\

An example or two using XL2BB would be helpful.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,309
Office Version
  1. 2016
Platform
  1. Windows
Hi MollyPJ,

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

MollyPJ.xlsx
ABCDEF
1CountTotal Qty
2FruitQtyChecked?25
3Apple2YES
4Apple4NO
5Orange6NO
6Banana8NO
7Apple3YES
Sheet1
Cell Formulas
RangeFormula
E2E2=COUNTIFS(C3:C7, "YES", A3:A7, "Apple")
F2F2=SUMIFS(B3:B7,C3:C7, "YES", A3:A7, "Apple")
 

Unlucky-Phase

New Member
Joined
Sep 23, 2019
Messages
14
1599080927833.png


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

Well-known Member
Joined
May 8, 2018
Messages
1,448
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

Does this help?

Book2
ABCDEFG
1ItemQtyWeek1Week2Week1Week2
29/2/20209/9/2020AppleApple
3Apple1YESYES58
4Apple4YESYES
5Apple3NoYES
6Banana3YESYES
7Pear1YES
Sheet3
Cell Formulas
RangeFormula
F3:G3F3=SUMPRODUCT((F2=$A$3:$A$7)*($B$3:$B$7)*("YES"=C$3:C$7))
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,448
Office Version
  1. 365
  2. 2010
You can change F1 and F2 in this example.

Book2
ABCDEF
1ItemQtyWeek1Week2Week2
29/2/20209/9/2020Apple
3Apple1YESYES8
4Apple4YESYES
5Apple3NoYES
6Banana3YESYES
7Pear1YES
Sheet3
Cell Formulas
RangeFormula
F3F3=SUMPRODUCT((F2=$A$2:$A$7)*($B$2:$B$7)*("YES"=INDEX(A2:D7,0,MATCH(F1,$A$1:$D$1,0))))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,388
Messages
5,601,376
Members
414,447
Latest member
CRAVIN

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
Top