Count of column C by checking column A

[nicdyb]

This one is probably quite easy, but I couldn't figure it out.

Anyways I do this report 3 times per week, and I count how much of our equipment is on rent to customers. Now I do it manually by just dragging my mouse down and seeing how many rows there are per location.

Basically we have two main categories; either on rent, or not allocated.

This is how my sheet looks like:

Please Login or Register  to view this content.

I would like the formula to count how many equipments per Location that is on rent versus how much is not on rent. There is 1 problem, the model above is simplified, I have many different customers per location. Therefore it has to count for instance ALL equipments on Location "A" and then count how many of these are "not on rent" and then figure out how many is on rent to customers (no need to have count per customer, just total on rent)

The output should be something like this;

On rent A 239
Not on rent A 312
On rent B 218
Not on rent B 254
On rent C 170
Not on rent C 190
On rent D 293
Not on rent D 81
On rent E 317
Not on rent E 177


Hopefully I dont suck at explaining :D

Thanks nicdyb

[rscsmith]

Hi nicdyb

You can use CountIfs to achieve your goal (only available in Excel 2007/2010. Use SumProduct if your are using Excel 2003).

The attached file shows you how.

Regards, Rob.

[nicdyb]

Not sure why its not working, but I guess its easier to see when you see my actual list. Guess I should have uploaded it in the first place =/

Thanks so far

Sheet 1 is how the data i exported from our systems. Sheet 2 is how I would like the output to be.

[nicdyb]

I tried editing your formula rscsmith, but I just kept getting 0 as a value...

[ShosMeister]

Try this. Simply put the countifs on your sheet 2.