2 Different counting functions on same sheet

[Davpmars]

I am working on a project where I need to easily calculate "visits" to companies in large quantities. I have attached to this Thread a sample of what I am working on. Currently I am able to put a number in the collumn labeled "Visit" and regardless what the number is, the appropriate numbers will calculate in cell B2 and B3. What I need to do is create a second function of some sort that will count the total number of vendors over $1,000 and the percentage that have been visited. I have set up a section for this in column C but I can't find a way to do the counting. This is only a sample that I have attached, the real form has thousands of entries.

Thanks in advance.

[Special-K]

Maybe

in C2
=SUMPRODUCT((B6:B14 > 1000)*(D6:D14 <> 0))

[oeldere]

maybe with an pivot table?

see the attached file.

[simeon.hruby]

Hi,

Here is what I was able to come up with.

1. In cell B1 it will now add up the total amount of vendors.
2. Columns J,K, and L will count 1 everytime the criteria of a vendor worth more than 1000.00 is true and if they have been visited or contacted by phone.
3. Cells B2, B3, and B4 will get their data based on the valuse in Columns J, K, and L.

You can hide the calculations in J, K, and L by changing the font color to white if you wish.

I hope this helps and let me know if you need anything else!


Simeon

[Davpmars]

Hi,

Here is what I was able to come up with.

1. In cell B1 it will now add up the total amount of vendors.
2. Columns J,K, and L will count 1 everytime the criteria of a vendor worth more than 1000.00 is true and if they have been visited or contacted by phone.
3. Cells B2, B3, and B4 will get their data based on the valuse in Columns J, K, and L.

You can hide the calculations in J, K, and L by changing the font color to white if you wish.

I hope this helps and let me know if you need anything else!


Simeon

Thank you all for your help. I found this sheet to be the most helpful given my limited experience with excel.

Can you now show me how to calculate a total number "Y" visits in column D and E for the whole? I need to be able to have calculations for both visits and percentages for vendors worth over 1000 and also visits and percentages for total visits as a whole not based on dollar amounts.

Thank you,

[Fotis1991]

Maybe

=COUNTIF(D6:D1000,"y")

=COUNTIFs(D6:D1000,"y",e6:e1000,"y")

[simeon.hruby]

Davpmars,

Sure that's no problem!
Going off of the spreadsheet that I posted for you all you have to do is use some COUNTIF functions like Fotis1991 said.

In cell C2 I used

Please Login or Register  to view this content.

the $ in front of the column letter and row number is used to make that cell or range absolute. In this case any "Y" in column D will becounted once and all of them will be added toghether.

In cell C3 I used

Please Login or Register  to view this content.

This is taking the number of vendors that were visited as calculated in the above formula and dividing it with the total number of vendors in cell B1, thus giving you a percentage of visited vendors.

In cell C4 I used

Please Login or Register  to view this content.

This works the same as the first formula but I am using Column E to count all of the cells that equal "Y" and then dividing that number with the total number of vendors to give you a percentage of vendors that were contacted by phone.

I have attached a spreadsheet with the updates on it. I hope this helps!


Simeon