Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | item | date | status | Open | Door | ||||
2 | Door | 1/1/2019 | open | 1/1/2019 | 1 | ||||
3 | Cab | 4/5/2019 | closed | ||||||
4 | Drawer | 2/7/2019 | open | ||||||
5 | Door | 5/1/2019 | closed | ||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | =SUMPRODUCT(--($A$2:$A$5=$G$1),--($C$2:$C$5=$F$1),--(MONTH($B$2:$B$5)=MONTH($E$2)),--(YEAR($B$2:$B$5)=YEAR($E$2))) |
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | item | date | status | CRITERIA 1 | CRITERIA 2 | CALCULATED NO. | |||
2 | Door | 01-01-2019 | open | Door | Open | 1 | |||
3 | Cabinet | 04-05-2019 | closed | ||||||
4 | Drawer | 02-07-2019 | open | ||||||
5 | Door | 05-01-2019 | closed | ||||||
6 | |||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G2 | G2 | =COUNTIFS($A$2:$A$5,$E$2,$C$2:$C$5,$F$2) |
Here is one way. Notice that I put Open and Door into separate columns.
Book1
A B C D E F G 1 item date status Open Door 2 Door 1/1/2019 open 1/1/2019 1 3 Cab 4/5/2019 closed 4 Drawer 2/7/2019 open 5 Door 5/1/2019 closed Sheet1
Cell Formulas Range Formula F2 F2 =SUMPRODUCT(--($A$2:$A$5=$G$1),--($C$2:$C$5=$F$1),--(MONTH($B$2:$B$5)=MONTH($E$2)),--(YEAR($B$2:$B$5)=YEAR($E$2)))
Will you like to have a helper column ?Thanks Ahoy but it ended up looking at only open cells at 01/01/19. I need it to look up every open cell in the month of jan-19.
Will you like to have a helper column ?
Hello I used a Helper column and I have made the calculation dynamic according to the criteria kindly have a look and please reply if it worked for youI'm not sure ER_Neha. Column E should be Jan-19, Feb-19, Mar-19 OR could be 1, 2, 3, 4.
It is a spreadsheet with 4000 rows
Book1 | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | item | date | status | Helper Column | CRITERIA 1 | CRITERIA 2 | CRITERIA 3 | CALCULATED NO. | |||
2 | Door | 01-01-2019 | open | Jan-19 | Door | Open | Jan-19 | 1 | |||
3 | Cabinet | 04-05-2019 | closed | May-19 | |||||||
4 | Drawer | 02-07-2019 | open | Jul-19 | |||||||
5 | Door | 05-01-2019 | closed | Jan-19 | |||||||
6 | |||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
I2 | I2 | =COUNTIFS($A$2:$A$5,$F$2,$C$2:$C$5,$G$2,$D$2:$D$5,$H$2) |
D2:D5 | D2 | =TEXT(MONTH(B2)&"-"&YEAR(B2),"mmm-yy") |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
H2 | List | =$D$2:$D$5 |
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | item | date | status | Open | Door | ||||
2 | Door | 1/1/2019 | open | 1/1/2019 | 1 | ||||
3 | Cab | 5/4/2019 | closed | 1 | |||||
4 | Drawer | 7/2/2019 | open | ||||||
5 | Door | 1/5/2019 | closed | ||||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F2 | F2 | =SUMPRODUCT(--($A$2:$A$5=$G$1),--($C$2:$C$5=$F$1),--(MONTH($B$9:$B$12)=MONTH($E$2)),--(YEAR($B$9:$B$12)=YEAR($E$2))) |
F3 | F3 | =COUNTIFS($A$2:$A$5,$G$1,$C$2:$C$5,$F$1,$B$2:$B$5,">="&$E$2,$B$2:$B$5,"<="&EOMONTH($E$2,0)) |