• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNT IF G10:G1000 equals today or the past, and U10:U1000 equals an empty cell.

indi visual

New Member
I use a basic countif to count the number of jobs due today. It counts the current date down the column and lets me know how many jobs are due that day.


However,


I noticed that if the job goes late into the next day, and the completed cell is empty, then the counter does not know that this job still needs to be accounted for.


If I had a formula with something along the lines of if the date due column is today or in the past, and the completed column is empty, then count.


I can use conditional formatting to highlight it red, but I want my counter to recognize past due jobs as well. Which means it would have to count based on multiple criteria.
 

Hui

Excel Ninja
Staff member
Indi

Have you tried Countifs?

Countifs allows counting based on multiple criteria

Have a read of http://chandoo.org/wp/2010/04/20/introduction-to-excel-sumifs-formula/

But use Countifs instead of Sumifs
 

indi visual

New Member
Yes and it works great.


=COUNTIF(G10:G1000, "=" & TODAY())


However, I'm not sure how to get it to count a row based on two conditions (the one above is just one).


If G10 is today's date or even further back into the past, and the U column is "empty", then count the number.
 

Hui

Excel Ninja
Staff member
COUNTIFS, not Countif


ie: =SUMIFS( Count Range, Criteria Range1, Citeria 1, Criteria Range2, Citeria 2, ... Criteria Range n, Citeria n)


eg: =COUNTIFS(G10:G1000, G10:G1000, "="& TODAY(), U10:U1000, "=""")
 

indi visual

New Member
I want it to do things.


Count the number of dates in column G that are today, and count the number of dates in column G that are today if there is nothing in column U.


The formula above you provided me works, but it ignores the first rule by itself.


Any work around on this one?
 

Hui

Excel Ninja
Staff member
Indi

Can you clarify "Count the number of dates in column G that are today, and count the number of dates in column G that are today if there is nothing in column U."


Isn't that the same as the number of dates in G which is Today ? or do you want 2 separate answers?
 

indi visual

New Member
I apologize for that it was a bit unclear.


If I make a countif rule with something along the lines of if G10:G1000=today's date, and U10:U1000 doesn't contain a date then count.[/example code]


and Next,


make another countif rule with something along the lines of [example code]if G10:G1000=any date, and U10:U1000 doesn't contain a date then count.[/example code]


then Finally,


I could make a third countif cell that would count the previous two countif functions together that displays the total sum. I could make 3 countif functions to frankenstein together the way I would like them to work.


However, I'm sure there is a single line formula that would achieve this task for me.
 

Hui

Excel Ninja
Staff member
Indi

G10:1000=Today and U10:1000<>"" : =COUNTIFS(G10:G1000,TODAY(),U10:U1000,"")

G10:1000="*" and U10:1000="" : =COUNTIFS(G10:G1000,"<>0",U10:U1000,"")


You can combine as

=COUNTIFS(G10:G1000,TODAY(),U10:U1000,"")+COUNTIFS(G10:G1000,"<>0",U10:U1000,"")
 

indi visual

New Member
Oh my wow thank you. You know, you've helped me all across the board on everything with this site. I really would like you to see what I've created (or shall I say what you've primarily helped me create).


Is there anyway I could upload you my work so you could check it out?


Out of curiosity with the combined code. Where did 42 and the 57 come from? I just wanted to know how you calculated that so I can modify it when necessary for future reference.
 

indi visual

New Member
Lol


I guess I misread. I had a long work day today.


Everything works beautifully. Here's what I'm doing exactly.


past incompletes +

present today incompletes +

equals = the number of jobs due


count ifs (G10:G1000) is the past date, and U10:U1000 is empty +plus

count ifs (G10:G1000) is today's date, and U10:U1000 is empty.


Your above code you provided works a million; however, I initially asked for the formula for a count if statement that contained "any date".


That was my mistake. I actually need that to be yesterday and forever into the past.


I tried to tweak your formula above to the one below to no avail.


=COUNTIFS(G10:G1000,<TODAY(),U10:U1000,"")
 

Hui

Excel Ninja
Staff member
Indi


This is great that you are starting to put it all together.

The logic behind spreadsheets is just as important if not more than the actual coding of the problem


ps: Your eyes are ok. I did post 42 and 57 but corrected my post.

I test all solutions I post but generally on the first piece of spare space I can find.

Then I change the Rows/Columns when I post the answers and fix up the ranges to match the problem and in this case missed those 2 numbers.


Try: =COUNTIFS(G10:G1000,"<"&TODAY(),U10:U1000,"")
 

indi visual

New Member
I think you as much as anybody knows how much knowledge I definitely lack, and I appreciate your patience with me.


You'd be impressed with me to know that I actually attached an alarm function to this code you provided me.


For every date that needs to be completed sounds off an alarm (or recorded text to voice wave) that says "there are such and such jobs remaining" : )


The alarm goes off every time the page calculates though (which means it's extremely annoying).


So I placed macro on a timer that fills the count cell in as blank to shut it up every second.


When the number changes based on the count if, the audio triggers, and then immediately shuts up again.


My coding is redundant, long, and messy (although it works), but my goal is to make it more clean and efficient.
 

Hui

Excel Ninja
Staff member
Have a read of http://chandoo.org/wp/2008/08/04/play-sound-when-cell-value-changes/

for methods to avoid the sound playing everythime a calculation happens
 

indi visual

New Member
I read the article. The information provided was similar to what I have (with the exception that reads changes and sounds the alarm at start up only).


However, I was wanting to know if there was anything I could do other than a wait timer (which is what I'm using now).


After every alarm, I have a d.wait timer that is set for 6 seconds, and then another macro that throws a blank value in the cell to shut the alarm up from repeating all the time.


However again, the wait timer is a bit slow and annoying as well, is there any alternative other than a wait timer that would give me the same desired effect?
 

Hui

Excel Ninja
Staff member
Indi

The macro I posted towards the end speaks every time the range of cells change
 

indi visual

New Member
It does, and I saved that function and used it for something else.


However,


That is a windows standard default beep, and I was looking for something more along the lines of this: I created these voice waves myself (they activate on the countif formula you helped me with).


http://www.iandmyself.me/webaudio/02.wav

http://www.iandmyself.me/webaudio/01.wav

http://www.iandmyself.me/webaudio/00.wav


All in all, I found a way to trigger them when I want them to sound off, but ultimately they are annoying.


But thanks in advance as always Hui!
 
Top