Formula Help

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,970
Office Version
  1. 2010
Platform
  1. Windows
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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 :)
 
Upvote 0
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
 
Upvote 0
In my opinion, the best way to do something like this is a Pivot table. Check the forums for more, or use Excel Help.
 
Upvote 0
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
 
Upvote 0
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"))
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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
Back
Top