COUNTIF - WTD, MTD, YTD and criteria

[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

 

[Frank Kabel]

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

 

[Norman Harker]

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.

 

[miker1999]

This is working perfect! Thank you...

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

 

[Norman Harker]

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.