Count cells with a specific value based on multiple conditions

D

dax2000

New Member
Joined
Sep 13, 2016
Messages
3
Hi,


I'm trying to count cells with a specific value based on multiple conditions on an excel sheet using SUM / COUNTIFS functions. I want to avoid writing scripts.

[TABLE="width: 832"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Color[/TD]
[TD="class: xl64, width: 64"]Jan-16[/TD]
[TD="class: xl64, width: 64"]Feb-16[/TD]
[TD="class: xl64, width: 64"]Mar-16[/TD]
[TD="class: xl64, width: 64"]Apr-16[/TD]
[TD="class: xl64, width: 64"]May-16[/TD]
[TD="class: xl64, width: 64"]Jun-16[/TD]
[TD="class: xl64, width: 64"]Jul-16[/TD]
[TD="class: xl64, width: 64"]Aug-16[/TD]
[TD="class: xl64, width: 64"]Sep-16[/TD]
[TD="class: xl64, width: 64"]Oct-16[/TD]
[TD="class: xl64, width: 64"]Nov-16[/TD]
[TD="class: xl64, width: 64"]Dec-16[/TD]
[/TR]
[TR]
[TD="class: xl63"]Red[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 2[/TD]
[/TR]
[TR]
[TD="class: xl63"]Green[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 1[/TD]
[/TR]
[TR]
[TD="class: xl63"]Blue[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 2[/TD]
[/TR]
[TR]
[TD="class: xl63"]Black[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Red[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 1[/TD]
[/TR]
[TR]
[TD="class: xl63"]Blue[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Black[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 3[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 2[/TD]
[TD="class: xl63"]Item 1[/TD]
[TD="class: xl63"]Item 1[/TD]
[/TR]
</tbody>[/TABLE]




What I'm trying to do is to get how many ITEM 1 with red color are on Jan-16 / Feb-16 etc. Expect results should be 2.




Thanks,
John
 
Sort by date Sort by votes
maybe...

Excel 2010
ABCDEFGHIJKLM
Color
RedItem 1Item 2Item 2Item 2Item 1Item 3Item 3Item 1Item 1Item 3Item 2Item 2
GreenItem 3Item 3Item 3Item 3Item 1Item 2Item 2Item 1Item 2Item 1Item 1Item 1
BlueItem 2Item 1Item 3Item 1Item 2Item 3Item 3Item 2Item 3Item 2Item 3Item 2
BlackItem 3Item 2Item 3Item 2Item 3Item 1Item 3Item 1Item 3Item 1Item 2Item 3
RedItem 1Item 3Item 2Item 3Item 1Item 2Item 2Item 2Item 1Item 2Item 3Item 1
BlueItem 1Item 1Item 2Item 1Item 2Item 1Item 3Item 2Item 2Item 3Item 1Item 3
BlackItem 2Item 3Item 3Item 2Item 3Item 2Item 1Item 2Item 1Item 2Item 1Item 1
ColorItemStartEnd
RedItem 1

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]16-Jan[/TD]
[TD="align: right"]16-Feb[/TD]
[TD="align: right"]16-Mar[/TD]
[TD="align: right"]16-Apr[/TD]
[TD="align: right"]16-May[/TD]
[TD="align: right"]16-Jun[/TD]
[TD="align: right"]16-Jul[/TD]
[TD="align: right"]16-Aug[/TD]
[TD="align: right"]16-Sep[/TD]
[TD="align: right"]16-Oct[/TD]
[TD="align: right"]16-Nov[/TD]
[TD="align: right"]16-Dec[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]16-Jan[/TD]
[TD="align: right"]16-Feb[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F11[/TH]
[TD="align: left"]=SUMPRODUCT((A2:A8=A11)*(B2:M8=B11)*(B1:M1>=C11)*(B1:M1<=D11))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
215
flashgordie
F
S
  • Question Question
Data filters - how to use multiple filters to select rows, without adding further columns
Replies
3
Views
147
Cubist
Cubist
ricafonyat
  • Solved
Formula Help - Inventory Allocation & Distribution
Replies
5
Views
803
iliace
iliace
A
  • Solved
copy data from listbox on form to sheet based on restrictions
2
Replies
14
Views
287
Ali M
A
C
  • Question Question
Create a new row for every date in row and create multiple copies of data to new worksheet based on cell number value for each date in the row VBA
Replies
0
Views
201
Chris258
C

Share this page

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