Help with IF/AND function

S

sharon

Can somebody please help me with this formula. I can't get it to work.

=COUNTIF($A:$A,">="&S7)-COUNTIF($A:$A,">"&R7),AND(SUM(N(FREQUENCY($F$68:$F$687,$F$68:$F$687)>0))))


I want it to sum the frequency if its between certian dates.


Sharo
 
H

Harlan Grove

Can somebody please help me with this formula. I can't get it to work.

=COUNTIF($A:$A,">="&S7)-COUNTIF($A:$A,">"&R7),
AND(SUM(N(FREQUENCY($F$68:$F$687,$F$68:$F$687)>0))))

I want it to sum the frequency if its between certian dates.
Click to expand...

You appear to want to summing the count of numbers in the range F68:F687
corresponding to dates in column A within a range of dates given by S7 and R7.
If so, try the following formula instead.

=SUMPRODUCT(($A$68:$A$687>=S7)*($A$68:$A$687<R7)*ISNUMBER($F$68:$F$687))
 
S

sharon

Harlan,

Thanks for your reply the formula works but I need it to count th
frequency of numbers between the range of F68:F687 corresponding to th
range of dates. I tryed putting the frequency function into you
formula but I couldn't get it to work. How can I get the frequenc
into the formula?


Sharo
 
H

Harlan Grove

Thanks for your reply the formula works but I need it to count the
frequency of numbers between the range of F68:F687 corresponding to the
range of dates. I tryed putting the frequency function into your
formula but I couldn't get it to work. How can I get the frequency
into the formula?
Click to expand...

I don't understand what you mean by the frequency of numbers. Perhaps if you
could provide a simplified example with no more than 12 observations and the
result you expect.

From your original message,
..> . . . SUM(N(FREQUENCY($F$68:$F$687,$F$68:$F$687)>0)) . . .
Click to expand...

There's no way to tell what you want from this. FREQUENCY(X,X) gives an array of
counts of entries in X plus an additional element which is always zero. That
said, N(FREQUENCY(X,X)>0) gives an array of 1s and 0s with 1s corresponding to
numbers in X, so SUM(N(FREQUENCY(X,X)>0)) *ALWAYS* gives the same result as
COUNT(X) (at least when entered in an array formula). It appears you want
something different. The only alternative I can think of is the array formula

=SUM(FREQUENCY(
IF(($A$68:$A$687>=S7)*($A$68:$A$687<=R7)*ISNUMBER($F$68:$F$687),$F$68:$F$687),
IF(($A$68:$A$687>=S7)*($A$68:$A$687<=R7)*ISNUMBER($F$68:$F$687),$F$68:$F$687)))
 
F

Frank Kabel

Harlan said:
...
..

I'll let someone else figure this out. I don't open file attachments.
Click to expand...

Hi Harlan
normally I also won't open attachments but I found this issue
interesting. Unfortunately I was only able to solve half of the
problem. If I understood it correctly sharon wants to evaluate the
number of unique entries in a range combined with two criteria. e.g.
.... D ... F
4 01/01/2004 500
5 01/04/2004 500
6 01/23/2004 501
7 02/01/2004 512
8 02/02/2004 501
9 02/04/2004 500
10 03/04/2004 500
11 03/04/2004 600


For January this would result in 2 unique entries , for February in 3.
I was able (adapting I think one of your previous post) to calculate
the following:
=SUM(1/MMULT((IF((D4:D11<=DATE(2004,1,31)),F4:F11)=TRANSPOSE(IF((D4:D11
<=DATE(2004,1,31)),F4:F11)))+0,ROW(F4:F11)^0))-1
entered as array formula.

Though this works fine for January I wasn't able to add a second
criteria. e.g.
=SUM(1/MMULT((IF(AND((D4:D11<=DATE(2004,2,29)),(D4:D11>=DATE(2004,2,1))
),F4:F11)=TRANSPOSE(IF(AND((D4:D11<=DATE(2004,2,29)),(D4:D11>=DATE(2004
,2,1))),F4:F11)))+0,ROW(F4:F11)^0))-1
returns #VALUE and I really don't know why :-(

As I'm quite curious how to solve this kind of problem maybe you'll
take over :))
Best regards
Frank
 
S

sharon

Okay then. Here is an example.

Date Doc#
Jan.2 1
Jan.3 1
Jan.6 2
Feb.2 3
Feb.3 3
Feb.4 4

# of Doc
Jan Feb
2 2

I need it to look up the date and if its in Jan then count the # o
Doc. but not the duplicates
 
F

Frank Kabel

O.K.
sometimes after writing down the question the solution is obvious. to
sharon: try the following formula to calculate your january unique
entries:
=SUM(1/MMULT((IF((D4:D11<=DATE(2004,1,31))*(D4:D11>=DATE(2004,1,1)),F4:
F11)=TRANSPOSE(IF((D4:D11<=DATE(2004,1,31))*(D4:D11>=DATE(2004,1,1)),F4
:F11)))+0,ROW(F4:F11)^0))-1
entered as array formula
change the DATE function for your other months.

Tested for your example data but where may be better solutions
Frank
 
H

Harlan Grove

sometimes after writing down the question the solution is obvious. to
sharon: try the following formula to calculate your january unique
entries:
=SUM(1/MMULT((IF((D4:D11<=DATE(2004,1,31))*(D4:D11>=DATE(2004,1,1)),
F4:F11)=TRANSPOSE(IF((D4:D11<=DATE(2004,1,31))*(D4:D11>=DATE(2004,1,1)),
F4:F11)))+0,ROW(F4:F11)^0))-1
entered as array formula
change the DATE function for your other months.
Click to expand...
...

OK, counting unique values subject to criteria. If the items to be counted would
always be numbers, this could be shortened to the array formula

=COUNT(1/FREQUENCY(IF((D4:D11>=DATE(2004,1,1))*(D4:D11<DATE(2004,2,1)),
F4:F11),F4:F11))

If the items to be counted could include text, takes a little trickery.

=COUNT(1/FREQUENCY(IF(IF((D4:D11>=DATE(2004,1,1))*(D4:D11<DATE(2004,2,1)),
MATCH(F4:F11,F4:F11,0)),MATCH(F4:F11,F4:F11,0)))

The MATCH(X,X,0) idiom comes from Leo Heuser in a thread about the mode of text
values.
 
F

Frank Kabel

Harlan said:
OK, counting unique values subject to criteria. If the items to be
counted would always be numbers, this could be shortened to the array
formula

=COUNT(1/FREQUENCY(IF((D4:D11>=DATE(2004,1,1))*(D4:D11<DATE(2004,2,1)),
F4:F11),F4:F11))

If the items to be counted could include text, takes a little
trickery.

=COUNT(1/FREQUENCY(IF(IF((D4:D11>=DATE(2004,1,1))*(D4:D11<DATE(2004,2,1
)),
MATCH(F4:F11,F4:F11,0)),MATCH(F4:F11,F4:F11,0)))

The MATCH(X,X,0) idiom comes from Leo Heuser in a thread about the
mode of text values.
Click to expand...

Hi Harlan
quite a simplification! Hope the OP has solved her problem now :)
Regards
Frank
 
H

Harlan Grove

...
...
If the items to be counted could include text, takes a little trickery.

=COUNT(1/FREQUENCY(IF(IF((D4:D11>=DATE(2004,1,1))*(D4:D11<DATE(2004,2,1)),
MATCH(F4:F11,F4:F11,0)),MATCH(F4:F11,F4:F11,0)))
Click to expand...
...

Appears to be a typo. Make that
=COUNT(1/FREQUENCY(IF((D4:D11>=DATE(2004,1,1))*(D4:D11<DATE(2004,2,1)),
Click to expand...
MATCH(F4:F11,F4:F11,0)),MATCH(F4:F11,F4:F11,0)))
 
S

sharon

Harlan,

I copied your formula and pasted it into the example and I'm getting
result of 0.



Frank,

I did the same with your formula and got #value!


Do these formulas work for you guys
 
F

Frank Kabel

Hi Sharon
both work but you have to enter them as array formulas! that is enter
them with CTRL+SHIFT+ENTEr instead of only hitting ENTER after pasting
the formula

I tried both in your spreadsheet and they'll work just fine. If you
still have problems, send me your sheet and I'll do the set-up for you

Frank
 
S

sharon

Thanks Frank. It works great. I should have thought of that. I gues
I'm getting tired.

Thank you guys for all your help. You guys are awesome. You should b
charging ppl for your help.


Sharo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Putting 2 Formulas together 2
Using SUM(INDIRECT()) 2
Sum every Nth Row (almost working) 2
Combination of IF, AND, and COUNTIF; need help 6
VLOOKUP & Data Validation 0
Badly need help with a function 4
Calculate average in intervals 1
Offset, Match Questiond 7

Top