countif + sumproduct

[bluepenink]

Hello

Having some problem with a formula.

In range O8:O1446, there are cells that contain: n/a

I want to basically do, if the cells in range O contain N/A, then

SUMPRODUCT(--('G.S - Report'!$L$8:$L$65536>=$D$8),--('G.S - Report'!$L$8:$L$65536<=$D$8+6),--('G.S - Report'!$O$8:$O$65536<='Drill Down'!N17))

else
0

can someone pls help!

 

[Joneye]

Can you post some of the data?

 

[bluepenink]

sorry but what do u mean..post some of the data?

 

[bluepenink]

any suggestions guys?

 

[T. Valko]

Hello

Having some problem with a formula.

In range O8:O1446, there are cells that contain: n/a

I want to basically do, if the cells in range O contain N/A, then

SUMPRODUCT(--('G.S - Report'!$L$8:$L$65536>=$D$8),--('G.S - Report'!$L$8:$L$65536<=$D$8+6),--('G.S - Report'!$O$8:$O$65536<='Drill Down'!N17))

else
0

can someone pls help!

any suggestions guys?

Is the N/A a TEXT entry or is it the Excel error value #N/A ?

What version of Excel are you using?

 

[bluepenink]

im using 03 and the n/a is not error, rather i have a formula in that range
i.e.
=IF(L1380=0,"n/a",NETWORKDAYS(I1380,L1380)-1)

so when it shows n/a

im trying to simply do the find the bucket the days fall under

eg.

# of services that took
2-5 days

if range O8:O1446 contains any n/a THEN

D10 = 6-June-2011 (it is the first monday of every week) +6 to get end of the week date.
therefore:

($D$10+6) - date requested for that invoice that shows n/a (date in column L) = # of days

so i would like then for it to be placed in its respective bucket?

can u pls help!

*pls ignore my previous sumproduct formula)*

 

[T. Valko]

im using 03 and the n/a is not error, rather i have a formula in that range
i.e.
=IF(L1380=0,"n/a",NETWORKDAYS(I1380,L1380)-1)

so when it shows n/a

im trying to simply do the sumproduct for the different criteria i have

eg.

# of services that took
2-5 days

if range O8:O1446 contains any n/a THEN

($D$10+6) - date requested for that invoice (date in column L) = # of days

so i would like then for it to be placed in its respective bucket?

can u pls help!

*pls ignore my previous sumproduct formula)*

Something like this...

=SUMPRODUCT(--(range1="n/a"),--(range2>=2),--(range2<=5),--(range3=$D$10+6))

 

[bluepenink]

hello isnt it suppose to be

if 'G.S - Report'!$O$8:$O$65536="n/a",

(($D$8+6)-'G.S - Report'!$I$8:$I$65536) = i.e. 3

so we now know this will fall b/w 2-5 days

so sumproduct = 1 invoice that falls between 2-5 days

.....is this doing the same as the formula you provided? thx u sir!

 

[T. Valko]

hello isnt it suppose to be

if 'G.S - Report'!$O$8:$O$65536="n/a",

(($D$8+6)-'G.S - Report'!$I$8:$I$65536) = i.e. 3

so we now know this will fall b/w 2-5 days

so sumproduct = 1 invoice that falls between 2-5 days

.....is this doing the same as the formula you provided? thx u sir!

If those are the requirements then:

=SUMPRODUCT(--('G.S - Report'!$O$8:$O$65536="n/a"),--(($D$8+6)-'G.S - Report'!$I$8:$I$65536>=2),--(($D$8+6)-'G.S - Report'!$I$8:$I$65536<=5))

 

[bluepenink]

Hello sir

thx u so much and for your prompt response.

just so i dont make an error, as it is a pretty big file.

i used the following as well

=SUMPRODUCT(--('G.S - Report'!$O$8:$O$65536="n/a"),--(($D$8+6)-'G.S - Report'!$I$8:$I$65536=$N19))

where N19 = 6
and then i dragged the formula down as the N20 = 7 etc.

thxs for the help.