Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Hide formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |