If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count blanks by date
I have a table that has meter# , date, and usage in colums A, B and C.
I use a formula =sumif($B$11:$B1000,"="[date],$C$11:$C1000) to total up the amount of usage for each day. But what I want to do is count the number of blanks in column C for each date. So "if $B$11:$B1000,"="[date] then countblank($C$11:$C1000)" is kind of what I'm shooting for but I can't wrap my brain around how to do it. I would like to report that on 4/1 we had 1,912 usage and 4 meters did not report (were blank). Thanks! |
#2
|
|||
|
|||
Count blanks by date
Try
=SUMPRODUCT(--($B$11:$B1000=[date]),--(ISBLANK($C$11:$C1000))) -- HTH Bob "Molasses26" wrote in message ... I have a table that has meter# , date, and usage in colums A, B and C. I use a formula =sumif($B$11:$B1000,"="[date],$C$11:$C1000) to total up the amount of usage for each day. But what I want to do is count the number of blanks in column C for each date. So "if $B$11:$B1000,"="[date] then countblank($C$11:$C1000)" is kind of what I'm shooting for but I can't wrap my brain around how to do it. I would like to report that on 4/1 we had 1,912 usage and 4 meters did not report (were blank). Thanks! |
#3
|
|||
|
|||
Count blanks by date
You can use sumproduct for multiple criteria
For your specifics here, try something like this: =SUMPRODUCT(($B$11:$B1000=--"15 Apr 2010")*($C$11:$C1000="")) where criteria 1 is $B$11:$B1000= --"15 Apr 2010" (shows an unambiguous way to use when it comes to dates data) criteria 2 is $C$11:$C1000="" (range="") is equivalent to countblanks The multiplication of the 2 criteria gives an "AND" result, where both criteria are satisfied. Success? hit the YES below -- Max Singapore --- "Molasses26" wrote: I have a table that has meter# , date, and usage in colums A, B and C. I use a formula =sumif($B$11:$B1000,"="[date],$C$11:$C1000) to total up the amount of usage for each day. But what I want to do is count the number of blanks in column C for each date. So "if $B$11:$B1000,"="[date] then countblank($C$11:$C1000)" is kind of what I'm shooting for but I can't wrap my brain around how to do it. I would like to report that on 4/1 we had 1,912 usage and 4 meters did not report (were blank). Thanks! |
Thread Tools | |
Display Modes | |
|
|