Formula should count sheet1 and show in sheet2

**[email protected]** · 06-07-2013, 03:03 AM

IN SHEET1 ONE THERE ARE VALUE OF A , B , C , D , E , F, G, H, I, J, K, MULTIPLE TIMES MEANS THERE ARE MANY TIME "A" "B" AND --ON
I WANT A FORMULA SO THAT ON SHEET2 WHEN I TYPE "A" IN COLUMN A1 IT SHOULD COUNT ALL THE A OF SHEET1 AND DOSPLAY THE COUNT IN SHEET2 COLUMN B OR WHEN I TYPE "B" IN COLUMN A1 IT SHOULD COUNT ALL THE B OF SHEET1 AND DOSPLAY THE COUNT IN SHEET2 COLUMN B--AND--ON
IF ANYBODY CAN TELL HOW TO DO THIS PROBLEM

**:) Sixthsense :)** · 06-07-2013, 03:09 AM

Use countif like this...

In Sheet2

In A2 Cell
A

In B2 Cell

Formula:

Please Login or Register  to view this content.

**dilipandey** · 06-07-2013, 03:11 AM

IN SHEET1 ONE THERE ARE VALUE OF A , B , C , D , E , F, G, H, I, J, K,

where in sheet 1 ? any particular column / row / range ? or anywhere in sheet 1 ?
can you upload a sample workbook?

Regards,
DILIPandey
<click on below * if this helps>

**[email protected]** · 06-07-2013, 03:34 AM

THANK'S IT WORK
BUT I WANT TO ASK ONE EXTRA POINT WHAT IF I HAVE MULTIPLE SHEET
for eg:- i have --sheet1, sheet2, sheet3, sheet4 with same value
when i type "A"in column A1 it should calculate "A" from all four sheet and display the count in sheet 5
pls help with this

**:) Sixthsense :)** · 06-07-2013, 03:42 AM

Just duplicate the formula and add it like this...

Formula:

Please Login or Register  to view this content.

Or

Use =COUNTIF.3D(3D range,Criteria) function of morefunc addin.

=COUNTIF.3D(Sheet1:Sheet5!A1:A200,A1) counts the values in Sheet1:Sheet5!A1:A200 which is equal to Cell A1.