Add another criteria to a countif routine

[ExcelRonin]

Hi

A member provided the following code to count the contents of WorkSpace!CC based on the list at WorkSpace!G2 (G2-G11) and output results to DATA!D8-D17.

Please Login or Register  to view this content.

In plain English this routine counts the number of Units in Workplace!C:C based on the list at WorkPlace!G2 and then outputs the results to Data!D8:D17.

I would like to count how many males/females/Not recorded are at each Unit. This data is in (WorkPlace!E:E). I have a list at H2 with Male/Female/Not recorded. Males will be stored at Data!D33:D42, Females at Data!E33:E42 & Not Recorded at Data!F33:F42

Hopes this make sense. Any assistance would be appreciated

Cheers

[Kaper]

If previous one worked as expected you may try:

Please Login or Register  to view this content.

(not tested, as there was no sample file)

[ExcelRonin]

Hi,

thanks for responding. No luck with the code. When I run it, it opens Update Values: Workplace dialog box which seems to want me to upload a file. When I click cancel it closes the dialog box and displays #VALUE! in the output cells. D33 has the formula =COUNTIFS(WorkSpace!C:C,WorkSpace!G2,[WorkPlace]WorkPlace!E:E,TRANSPOSE(WorkSpace!H2)).

Cheers

[Kaper]

Check carefully sheet names - do you have both WorkPlace and Workspace - you refer to both in post #1

... in Workplace!C:C based on the list at WorkPlace!G2 ...

And as I said - no sample file attached = code not tested See 5th bullet point below numbered rules in http://www.excelforum.com/forum-rule...rum-rules.html

There is a big chance that as you try to fill right such simple trick as transform would not do. Try (If I was right about your mistake with WorkPlace/WorkSpace):

Please Login or Register  to view this content.

[ExcelRonin]

Hi,
When I click the Attachments Icon I get the following 18-03-2016 3-13-56 PM.png with no dialog to upload from my local drive. Any ideas?


cheers.

[FDibbins]

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[ExcelRonin]

I have followed the instructions> I can select the file from my local drive and upload the file

[ExcelRonin]

I have followed the instructions. I can select the file from my local drive and upload the file but cannot locate the done option. The only option I have is to close the page and return to this page. Where I have no indication that a file is attached. Frustrating mechanics.

[sysss]

Hi,

you did well. File is uploded

[ExcelRonin]

Well, I'll be damned it uploaded twice. This forums mechanics will take some getting use to. Never mind the assistance is worth it. Thanks for the guide.

You were right I had WorkPlace where I indeed should have had WorkSpace. My dumb.

I corrected first code and ran the sheet again. It posted the correct numbers for the first 2 Male counts. All other outputs where 0. In all cases the Range H:xx had incremented by 1.

cheers,

[Kaper]

As you can see Macro 4 works fine. So discard macro3.

A standard tool in such cases would be pivot table. It could be used also to prepare tables with age and years in service tables.

I used it (pivot table) to check output from macro4 - see cells J1 and down/right.

This whole thread shows how attached sample sheet make life easier.

[ExcelRonin]

Thank you for your assistance. I've haven't got to pivot tables yet but its on the list.

I'll mark this as solved. I wasn't able to add to your rep...It seems I need to spread the love around

cheers