COUNTIF - WTD, MTD, YTD and criteria
miker1999 <>
I would like to return results from a spreadsheet in which I can report
# 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 just
need to count the filled ones. You can tell the filled ones by column
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 the
week, month, year-to-dates.
Help?
---
Message posted from http://www.ExcelForum.com/
Frank Kabel
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
--
Regards
Frank Kabel
Frankfurt, Germany
Norman Harker
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
njha...@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"miker1999 >" <<miker199...@excelforum-nospam.com> wrote in
message news:miker199...@excelforum-nospam.com...
miker1999 <>
My next question... can I use these formula crossing between
worksheets? Formula on one sheet...data on another?
Norman Harker
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.
njha...@optusnet.com.au
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"miker1999 >" <<miker199...@excelforum-nospam.com> wrote in message
news:miker199...@excelforum-nospam.com...
