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 = S

So 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

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

andK3 = AR2.

2 - 0For example, when both D3 = AR1

andK3 = 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.

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.

Look at the SUMPRODUCT function. Perhaps something like:

=SUMPRODUCT(($A$3:$A$519 = "01-MAR-11")*($D$3:$D$519 = AR1)*($K$3:$K$519 = AR2))

2 notes:

1 - The Excel help file for SUMPRODUCT does not do the function any justice. It is much more robust than it appears. I suggest that you Google the function and read some of the information found on the web, especially in other forums where people have used it in real life situations.

2 - Setting the first criteria (= "01-MAR-11") might be troublesome depending on how Excel interprets the string. When you put it in quotes it becomes a Text string and SUMPRODUCT might not recognize it as a date when evaluating the formula.

To avoid confusion, I would try to understand the SUMPRODUCT function without using dates to make sure I could get it working for three criteria, maybe something like this:

=SUMPRODUCT(($A$3:$A$5 = "Test")*($D$3:$D$5 = AR1)*($K$3:$K$5 = AR2))

Once I knew that I was getting the results I expected, I would start playing with the Date portion to see if it still worked.

If you get stuck, come on back.

Keep in mind that we can't see your spreadsheet from where we're sitting, so just saying things like "Your suggestion doesn't work" won't help us help you. You'll need to be specific, perhaps posting some example data.

If you need to post data, please read the How To referenced in my signature line before hand.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History