best formula to use?
 

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

best formula to use?
 

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" wrote in
message ...

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

best formula to use?
 

Hi!

Try this:

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

Biff

"darkwood" wrote in
message ...

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

best formula to use?
 

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!


--
darkwood
------------------------------------------------------------------------
darkwood's Profile: http://www.excelforum.com/member.php...o&userid=29948
View this thread: http://www.excelforum.com/showthread...hreadid=503100