# EXCEL COUNTIF Formula Help February 19, 2011 at 11:27:56
Specs: Windows 7
 I have worked out a formula for countif but I am struggling with getting it to work in the follwoing manner.I have a Log whereby I need to extract information based on certain values by date!Column A1:A519 is date as entered.I have to count how many times during a date a certain value based on 2 values occurs.I have made hidden columns for the values I need and date columns up to 31.The formula I have used is: =COUNTIF(A3:A519,"=01-MAR-11")*(D3=AR1)*(K3=AR2)However when I change date it does not insert into the specific date column (hidden) and it relies on D3 & K3, however I need it to recognise the column D3:D519 with AR1 and K3:K519 with value AR2.All values are text not sums, e.g. D3 = BIH & K3 = SSo wherever I enter e.g. 01-MAR-11 in Column A3:A519 I need it to register in hidden column AR4 and if date entered is e.g. 28-MAR-11 then I need it to add all of those together the in hidden Column AR31.Please Help - megga novice here.Thanks in advance See More: EXCEL COUNTIF Formula Help

#1 February 19, 2011 at 17:36:47
 Before I try to figure out what you are trying to do - and I'll admit that I'm pretty confused by your post - let's start with what your formula is going to do. =COUNTIF(A3:A519,"=01-MAR-11")*(D3=AR1)*(K3=AR2)Let's break that into pieces:=COUNTIF(A3:A519,"=01-MAR-11")That's pretty straight forward. It'll count how many times that date appears in the range.D3=AR1This will either return 1 or 0 (TRUE or FALSE)1 (TRUE) if D3 = AR1, 0 (FALSE) otherwise.K3=AR2Same as above.So, what you are going to get is either:1 - The COUNT of 01-MAR-11 only when both D3 = AR1 and K3 = AR2.2 - 0For example, when both D3 = AR1 and K3 = AR2 you'll have:=COUNTIF(A3:A519,"=01-MAR-11")*(1)*(1)If one or the other isn't TRUE, you'll get:=COUNTIF(A3:A519,"=01-MAR-11")*(0)*(1)or=COUNTIF(A3:A519,"=01-MAR-11")*(1)*(0)which will both equal 0.Is that what you are looking for?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
February 20, 2011 at 04:38:52
 DerbyDad03, Thanks for your reply.In essence I think yes, however the data in AR1 could appear in any of the range of column D from D3 through to D519 and likewise AR2 in Column K from K3 through to K519.How would the formula look now to ensure that if they both match in any of the ranges with the specified date it appears as 1, 2, 3, 4 etc added together.I need the data in D and K to match and be counted on a specified date, so if the date returns no matches in D & K columns combined then a O is returned.

Report •

#3 February 20, 2011 at 07:43:13 