Count by remark ,status

[mathanraj76]

Hi all
Good days

I have data in excel file
Its have 3 sheets
1st Sheet is "Data"
2nd Sheet is "output"
3rd Sheet is "all status"

First I need to count total status code and remarked by user for "output"
second Need to count total all status code and remarked by user for "all status"


Everyday I will received data and need prepare all this output and all status as daily routine task

I have attached sample data


Thanks
Raj

[BadlySpelledBuoy]

Put this formula in "Output B2" then copy right and down as required:

Please Login or Register  to view this content.

Put this in "all status B2" then copy right and down:

Please Login or Register  to view this content.

Or you could use Pivot Tables.

BSB

[mathanraj76]

Hi
I am very appreciate your help
thanks for your reply

For "all status" the formula is not working
where it's get "0"
Thanks

[BadlySpelledBuoy]

Note that "Amalina" is spelled incorrectly on the 'all status' sheet and will need amending before those numbers show correctly.

BSB

[mathanraj76]

Hi
yes you correct and its working

How if i need to write this formula into excel macro
Thanks

[BadlySpelledBuoy]

User the 'Macro Recorder' whilst entering the formula and Excel will convert it into a format you can use in VBA'

BSB

[mathanraj76]

Hi

Thanks for your solutions you are so good in excel

Please advice me in here

I write in excel module

Worksheets("all status").Range("B2:AK42").Formula = "=COUNTIFS(Data!$J:$J,B$1,Data!$H:$H,$A2)"

Is that a way for me just show the result and not fomula in each cells

[BadlySpelledBuoy]

In the attached there is a "Update Tables" button on a sheet called Control. Clicking that will fire the code below and will add the formulas provided earlier to the two tables then paste their values (getting rid of the formulas) after.

Please Login or Register  to view this content.

I've also included two new sheets with Pivot Tables that is perhaps the easier option for this.

BSB