Countif problem

[Busymanjohn]

Hey guys, this one is racking my brains ,,, i have a table of data, col B has dates from 11-dec-11 to 17-dec-11 ( 5000 rows ), with the dates appearing many many times each. in col G i have costs ranging from zero to >$5000 ,,,, what I want to extract is a table with a count of, by day ( so 11-dec-11 to 17-dec-11 )where the cost is zero,between zero and 500, between 500 and 1000, between 1000 and 2000, between 2000 and 5000 and > 5000 ,,, so that the final table will show the count of each cost range for each day ,,, tried using various methods of countif ,,, but nothing worked, any ideas??

 

[Hui]

Busymanjohn

Countif cannot do what you want


What about this format

=COUNTIFS(B2:B5000,"4/2/2012",G2:G5000,">"&5000,G2:G5000,"<="&10000)


or


=SUMPRODUCT((B2:B5000=DATE(2012,2,4))*(G2:G5000>5000)*(G2:G5000<=10000))

 

[prasaddn]

Hi Busymanjohn, Best and method would be to use Pivot table and group as per your wish and use count function, as by default it will take sum.

 

[Busymanjohn]

Thanks Hui ,, with a couple of tweaks ,, works a treat. prasaddn, nice idea with the pivot tables ,, i personally am not a fan of them, so tend to steer clear, but will give it a go and see how it looks. Thanks again.

 

[Busymanjohn]

Hi guys, back again ,,, had a slight problem with the COUNTIFS formula, the solution was for one of my colleagues and has Excel 2003 only, so obviously the COUNTIFS does not work. How can I solution this with 2003 version? remember col B has the dates ( in format 11-Dec-11, 12-Dec-11 etc ) and column G has the costs, ranging from no cost to > $5000, the result needs to be a table ( with dates down one side ) and a count of how many times the cost = no cost in one cell for a particular date, say 11-Dec-11, how many times the cost is <$500 in another cell ( same date ), how many times the cost is >$500 but <$1000 in another cell, etc etc. Thanks

 

[Faseeh]

Hi Busymanjohn,


Try out sumproduct() formula from Hui's post it will work in Excel 2003.


Faseeh

 

[Busymanjohn]

Hi, I did use the SUMPRODUCT() formula but found that one of the ranges gave me incorrect result .... =SUMPRODUCT((B$2:B$5080=DATE(2011,12,11))*(G$2:G$5080>5000)) ... result should be 0 ,,, but i get a result of 96, which is the same result I get for blanks ( or no cost ),,, can you point me in the direction to correct this ,, all other ranges work fine. Thanks

 

[Busymanjohn]

Hi guys ,,, I went with an extended version of the formula ... =SUMPRODUCT((B$2:B$5080=DATE(2011,12,11))*(G$2:G$5080>=5000)*(G$2:G$5080<100000)) ,,, rather than cap it at >=5000, this works fine, although I would still like to understand why the shortened version did not give the correct result

 

[Hui]

BusymanJohn


Your first formula

=SUMPRODUCT((B$2:B$5080=DATE(2011,12,11))*(G$2:G$5080>5000))

Will return all records where Date = 2011,12,11 (yy,mm,dd) [b]and Column G>5000


Your second formula

=SUMPRODUCT((B$2:B$5080=DATE(2011,12,11))*(G$2:G$5080>=5000)*(G$2:G$5080<100000))

will return date = 2011,12,11 and Column G>5000 and[/b] Column G<100000

 

[Busymanjohn]

hey Hui ,,, I understand that the first formula should return the result based on the 2 criteria, but the result returned ( 96 ) is incorrect, there are no costs >5000 in G2:G5080 with the date 2011,12,11 in B2:B580 ,,, the result should be zero .... and 96 is the number of blank ( or no cost ) in G2:G5080. This is why I decided to add the <1000000 in the second formula ,,, to return the correct result. Just unsure why the first formula returned the incorrect result.

 

[Hui]

John

Any chance of emailing me the file?

Click on Excel Ninja to the left

email at Bottom of the page

 

[Hui]

This was a strange one which will require some more time to analyse


However I do have a solution even though I am not sure what the problem is ?


Solution:

in P2: put

=SUMPRODUCT((B$2:B$5080=DATE(2011,12,11))*(G$2:G$5080>=5000)*(G$2:G$5080<>""))


Problem:

The problem is that the cells in Column G which look blank aren't blank!

Before you use the above formula move to a cell in Column G and press Del !


But I can't tell you what is in Column G either?


However the above solution fixes it


I will investigate and report back