Formula Help

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Customer Services MI Sep 06+.xls
LMNO
1DATEHDISSUEDEPT
202/10/200600:00YesDocMan 
302/10/200600:00Unknown 
403/10/200600:00Unknown 
503/10/200600:00YesDocMan 
603/10/200600:00YesFE 
704/10/200600:00YesFE 
804/10/200600:00Unknown 
904/10/200600:00Unknown 
1004/10/200600:00Unknown 
1104/10/200600:00NoN/A 
1205/10/200600:00Unknown 
1305/10/200600:00NoN/A 
1405/10/200600:00NoN/A 
1505/10/200600:00YesFE 
1606/10/200600:00NoOther 
SQL Query 3


I have the table above in my workbook

What i would like to record (somehow) is the amount of Yes' for each department (the table is much longer)

Whats the best way to do this?

Thanks
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows
Hi,

Try:

=SUMPRODUCT(--(N2:N100="Doc Man"),--(M2:M100="Yes"))

You need to expand / reduce ranges to suite.
Also can change "Yes" & "Doc Man" to cell references.

Regards,
Jon :)
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Hi,

Try:

=SUMPRODUCT(--(N2:N100="Doc Man"),--(M2:M100="Yes"))

You need to expand / reduce ranges to suite.
Also can change "Yes" & "Doc Man" to cell references.

Regards,
Jon :)

Jon - Can i replace "Doc Man" with a cell reference? EG R14

Thanks
 

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
In my opinion, the best way to do something like this is a Pivot table. Check the forums for more, or use Excel Help.
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
In my opinion, the best way to do something like this is a Pivot table. Check the forums for more, or use Excel Help.

Thanks, I am familiar with Pivot Tables, will investigate
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
Hi,

Try:

=SUMPRODUCT(--(N2:N100="Doc Man"),--(M2:M100="Yes"))

You need to expand / reduce ranges to suite.
Also can change "Yes" & "Doc Man" to cell references.

Regards,
Jon :)

Jon - Have adapted to suit

Thanks

=SUMPRODUCT(--($N$2:$N$100=R14),--($M$2:$M$100="Yes"))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,519
Messages
5,529,315
Members
409,863
Latest member
stacy09
Top