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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,845
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"))
 

Forum statistics

Threads
1,144,619
Messages
5,725,331
Members
422,617
Latest member
rahul27ragit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top