COUNTIF - WTD, MTD, YTD and criteria

  • Thread starter Thread starter miker1999
  • Start date Start date
M

miker1999

Hello,
I would like to return results from a spreadsheet in which I can repor
# of filled jobs that occured:
Week to Date (of the current week we are in)
Month to Date (of current month)
Year to Date

The spread sheet has all jobs on it - both open and filled. I jus
need to count the filled ones. You can tell the filled ones by colum
K = "FILLED".

The date of the job offer is in column B.

So far, I only have =COUNTIF(K:K,"FILLED"). But I need to add in th
week, month, year-to-dates.

Help
 
Hi
you may try
=SUMPRODUC((K1:K1000="FILLED)*(B1:B1000>=DATE(YEAR(TODAY),MONTH(TODAY),
1))*(B1:B1000<DATE(YEAR(TODAY),MONTH(TODAY)+1,1)))
to get the current month
or
=SUMPRODUC((K1:K1000="FILLED)*(B1:B1000>=DATE(YEAR(TODAY),1,1))*(B1:B10
00<DATE(YEAR(TODAY),MONTH(TODAY),DAY(TODAY))))
for YTD counts
 
Hi Miker1999

Here's three formulas:

=SUMPRODUCT((INT((B1:B1000-DATE(YEAR(TODAY()),1,1))/7)+1=INT((TODAY()-
DATE(YEAR(TODAY()),1,1))/7)+1)*(K1:K1000="FILLED"))
Returns number of filled contracts in current week where the week
numbering system uses Jan 1 = day 1 of week 1 and jan 8 = day 1 of
week 2

=SUMPRODUCT((MONTH(B1:B1000)=MONTH(TODAY()))*(YEAR(B1:B1000)=YEAR(TODA
Y()))*(K1:K1000="FILLED"))
Returns number of filled contracts for the current month of the
current year.

=SUMPRODUCT((YEAR(B1:B1000)=YEAR(TODAY()))*(K1:K1000="FILLED"))
Returns number of filled contract for the current year.

SUMPRODUCT does not accept an entire column as an argument.

You might find a Pivot table would be a much better and much more
flexible solution:

Debra Dalgleish written Jon Peltier hosted:
http://peltiertech.com/Excel/Pivots/pivotstart.htm


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
This is working perfect! Thank you...

My next question... can I use these formula crossing between
worksheets? Formula on one sheet...data on another?
 
Hi miker1999!

Yes! But you need to add the sheet references.

Best way is perhaps to build (or edit) the formulas by a process of
navigating to and selecting the entries you want in your formulas.

If copying these formulas down or across, make sure that you absolutely
reference the B and K ranges.



--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top