Count based on criteria

LUIS FREUND
LUIS FREUND used Ask the Experts™
on
Wanted to count values based on certain criteria.   For example per the attachment.

The count is on the "MAT_REC_BY_MONTH" tab and the data is on the "DATA" Tab.  I've added some values on Week 1 and week 2 to show what the values should be.

On "MAT_REC_BY_MONTH" tab:  Column D (REC), the count is based on the "DATA" Tab Column Z filtered on "blanks" on Column AB for Calendar WK 1 on column Y.
On "MAT_REC_BY_MONTH" tab:  Column E (REJ), the count is based on the "DATA" Tab Column Z filtered on "YES" on Column AB for Calendar WK 1 on column Y.

Wanted a formula to count for all the weeks listed on the "MAT_REC_BY_MONTH" tab.  See attachment.
C--Users-lfreund-Downloads-COUNT-EE.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Please try this...

In D3
=SUMIFS(DATA!$Z$2:$Z$37490,DATA!$Y$2:$Y$37490,$C3,DATA!$AB$2:$AB$37490,"")

Open in new window


In E3
=SUMIFS(DATA!$Z$2:$Z$37490,DATA!$Y$2:$Y$37490,$C3,DATA!$AB$2:$AB$37490,"Yes")

Open in new window

And then copy both the formulas down the rows.
C--Users-lfreund-Downloads-COUNT-EE.xlsx
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018
Commented:
Hi Luis,

Please find attached....actually you needed the sum not the count :)

Oops forgot to attach...:)
C--Users-lfreund-Downloads-COUNT-EE.xlsx

Author

Commented:
Awesome!   Thank  you both for the solution!   Happy New Year to both!
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Luis!
Thanks and same to you too. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial