best formula to use?

[darkwood]

I've got 2 cells that I need to fill in multiple areas of a spreadsheet. One involves meeting criteria from 2 columns on my corresponding data page, and the other, meeting 3 criteria. The report is for referrals sent by one location to another location. So, in Column D is the department that the employee works in (52 for example), and column C is the department that they sent the referral to (62 for example). If there are multiple referrals by dept 52 to dept 62, they show up individually on the report. So, the first formula I tried to write, which didn't work was: =IF(Data!$D$2:$D$200="52",COUNTIF(Data!$C$2:$C$200,62),0)

What I want it to say is that if department 52 sent a referral to dept 62, how many were sent? Would =SUMPRODUCT work in this case? If so, how?

The second formula involves the first formula, but also adds that Column B Must equal "Closed-Q-B", and then the rest of the above formula.

HELP?

Thanks,

-S

[Bob Phillips]

Yes SP would work

=SUMPRODUCT(--(Data!$D$2:$D$200="52"),--(Data!$C$2:$C$200=62))

shouldn't you also test against number 52 not text "52"?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"darkwood" <[email protected]> wrote in
message news:[email protected]...
>
> I've got 2 cells that I need to fill in multiple areas of a spreadsheet.
> One involves meeting criteria from 2 columns on my corresponding data
> page, and the other, meeting 3 criteria. The report is for referrals
> sent by one location to another location. So, in Column D is the
> department that the employee works in (52 for example), and column C is
> the department that they sent the referral to (62 for example). If
> there are multiple referrals by dept 52 to dept 62, they show up
> individually on the report. So, the first formula I tried to write,
> which didn't work was:
> =IF(Data!$D$2:$D$200="52",COUNTIF(Data!$C$2:$C$200,62),0)
>
> What I want it to say is that if department 52 sent a referral to dept
> 62, how many were sent? Would =SUMPRODUCT work in this case? If so,
> how?
>
> Thanks,
>
> -S
>
>
> --
> darkwood
> ------------------------------------------------------------------------
> darkwood's Profile:
http://www.excelforum.com/member.php...o&userid=29948
> View this thread: http://www.excelforum.com/showthread...hreadid=503100
>

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(Data!$D$2:$D$200=52),--(Data!$C$:$C$200=62))

Biff

"darkwood" <[email protected]> wrote in
message news:[email protected]...
>
> I've got 2 cells that I need to fill in multiple areas of a spreadsheet.
> One involves meeting criteria from 2 columns on my corresponding data
> page, and the other, meeting 3 criteria. The report is for referrals
> sent by one location to another location. So, in Column D is the
> department that the employee works in (52 for example), and column C is
> the department that they sent the referral to (62 for example). If
> there are multiple referrals by dept 52 to dept 62, they show up
> individually on the report. So, the first formula I tried to write,
> which didn't work was:
> =IF(Data!$D$2:$D$200="52",COUNTIF(Data!$C$2:$C$200,62),0)
>
> What I want it to say is that if department 52 sent a referral to dept
> 62, how many were sent? Would =SUMPRODUCT work in this case? If so,
> how?
>
> Thanks,
>
> -S
>
>
> --
> darkwood
> ------------------------------------------------------------------------
> darkwood's Profile:
> http://www.excelforum.com/member.php...o&userid=29948
> View this thread: http://www.excelforum.com/showthread...hreadid=503100
>

[darkwood]

Thanks everyone. SP was exactly what I needed. I did the text or number test and it was a number, so I removed the "", and it worked fine.

Thanks again!