counting when 2 criteria are met

[Edrick]

Hi. First, THANKS for offering this resource!

Now, my question...

I'm trying to count the number of instances that two criteria in a given row are met. Seems like it should be simple enough, but I haven't been able to get the formula right.

Here's an example...

column A........... column B
DESKTOP........... STORAGE
DESKTOP........... STORAGE
LAPTOP............. STORAGE
DESKTOP........... STORAGE
LAPTOP............. DEPLOYED
LAPTOP............. DEPLOYED
DESKTOP........... STORAGE

I want to show that...
# desktops deployed = 0
# desktops in storage = 4
# laptops deployed = 2
# laptops in storage = 1

What is the formula to do this?

THANKS!

 

[Joe4]

Try::
=SUMPRODUCT((A2:A65536="DESKTOP")*(B2:B65536="DEPLOYED"))
change your conditions for the other formulas.

 

[Greg Truby]

Or use a pivot table.

 

[Edrick]

That formula worked. THANK YOU!

I haven't ventured into pivot tables yet.

 

[Greg Truby]

I haven't ventured into pivot tables yet.

FYI -- I timed myself and it took me roughly all of eight (8) seconds to create the pivot table. (Took me a lot longer to duplicate your simple sample block.)

book3
	A	B	C	D
3	Count of column A	column B
4	column A	DEPLOYED	STORAGE	Grand Total
5	DESKTOP		4	4
6	LAPTOP	2	1	3
7	Grand Total	2	5	7
Sheet4