Change in formula

[aligahk06]

Dear All,

i want to add one more condition in formula based on attached sheet reference.

please see attached sheet and guide me

 

[vletm]

aligahk06
one possible would be something like this ...

 

[Peter Bartholomew]

My solutions are not similar to yours and I am not sure how you wish to report 'Closed' jobs ... but, for what it's worth, ...

= COUNTIFS(
  RM[Section], Section,
  RM[WO_Status], Status,
  RM[Status], "Open",
  RM[Report Date],">="&DATE(2020,9,1),
  RM[Report Date],"<="&EOMONTH(DATE(2020,9,1),0))

 

[aligahk06]

aligahk06
one possible would be something like this ...

aligahk06
one possible would be something like this ...

Thanks for time
Any solutionchanges in formula that u want to quote. ?

 

[aligahk06]

COUNTIFS(RM[Section], Section, RM[WO_Status], Status, RM[Status], "Open", RM[Report Date],">="&DATE(2020,9,1), RM[Report Date],"<="&EOMONTH(DATE(2020,9,1),0))

I tried but showing error in formula.

 

[aligahk06]

=COUNTIFS(
RM[Section],Mech,
RM[WO_Status],WMAT,
RM[Status], "Open",
RM[Report Date],">="&DATE(2020,9,1),
RM[Report Date],"<="&EOMONTH(DATE(2020,9,1),0))
Would u please elaborate the above formula. I tried but failed
I valued yout time and effort.
Thanks Sir.

 

[aligahk06]

Thank you sir,

Noe it is giving the desired result.

Thanks again for ur precious time.

 

[vletm]

aligahk06
here is that #2 sample file

 

[Peter Bartholomew]

@aligahk06
You would most likely be happiest with @vletm's solution. Your formula hard-wires the row and column heading text into individual formulas, meaning that each cell has to be hand-crafted. @vletm uses relative references (except for the date) meaning that the formula can be copied down and across. I have gone a step further and use defined names (I use conditional formatting to flag any direct reference as an error) and, wherever possible, I use array formulas. For me, with MS 365, the entire formula is filled by one formula held in the top left cell, but that probably takes you well outside your comfort zone! Re-committing the formula by selecting the table and using Ctrl/Enter will replicate the formula in each cell and it should still work as implicit intersection formulas.