CODEWITHSUNDEEP
vbacountmacrosfillcountif
Ananya Pandey
Ananya Pandey

Reputation: 157

Create macro to count from a sheet and fill the count in a table

Initial Information

I have sheet1 which has the data with multiple columnn, and I have named the data ranges as well.

enter image description here

My second sheet has kind of a dashboard where I need to put the count according to the values.

enter image description here

I have already Tried

  1. Countif - > gave #VALUE! error
  2. Countifs - > gave #VALUE! error
  3. sumif - > gave #VALUE! error
  4. sum(if(component="Abc",1,0)) - > Worked, but since its an array formula i have to press CTRL+SHIFT+ENTER

The problem with the last formula is that each time the data changes in sheet1 i have to go to sheet2 and press CTRL+SHIFT+ENTER on each cell. I need help with a Macro That can do the job.

Upvotes: 1

Views: 237

Answers (3)

Sabey
Sabey

Reputation: 11

Sno Status Component 1 WIP Abc 2 WIP Abc 3 WIP Mnp 4 WC Mnp 5 WC Mnp 6 RU Abc 7 RU Mnp

in one sheet starting from A1 cell

in the next sheet from A1 cell

Count Abc Mnp WIP =COUNTIFS(Sheet2!B2:B8,Sheet3!A2,Sheet2!C2:C8,Sheet3!B1) =COUNTIFS(Sheet2!B2:B8,Sheet3!A2,Sheet2!C2:C8,Sheet3!C1)

WC =COUNTIFS(Sheet2!B2:B8,Sheet3!A3,Sheet2!C2:C8,Sheet3!B1) =COUNTIFS(Sheet2!B2:B8,Sheet3!A3,Sheet2!C2:C8,Sheet3!C1)

RU =COUNTIFS(Sheet2!B2:B8,Sheet3!A4,Sheet2!C2:C8,Sheet3!B1) =COUNTIFS(Sheet2!B2:B8,Sheet3!A4,Sheet2!C2:C8,Sheet3!C1)

Upvotes: 0

pnuts
pnuts

Reputation: 59450

If Sno is in A1 and Count in G4 then in H6 copied across to I8: 

=COUNTIFS($B:$B,$G6,$C:$C,H$5)

or with appropriate sheet references added, for example: 

=COUNTIFS(sheet1!$B:$B,$G6,sheet1!$C:$C,H$5)

this could be placed on a different sheet from the data, and both formulae should work.

If not then perhaps you have a version of Excel that does not feature COUNTIFS, or your list delimiter is not , or something else is amiss that is neither function nor formula.

Upvotes: 0

Sabey
Sabey

Reputation: 11

Use COUNTIFS (RANGE OF Status in the record, current cell with Status, RANGE OF COMPONENT in the record, Current cell with Component) e.g:

COUNTIFS(B22:B28,A33,C22:C28,B32)


Sno Status  Component   

1   WIP Abc 
2   WIP Abc 
3   WIP Mnp 
4   WC  Mnp 
5   WC  Mnp 
6   RU  Abc 
7   RU  Mnp 

Count       Abc                                 Mnp 
WIP     =COUNTIFS(B22:B28,A33,C22:C28,B32)    =COUNTIFS(B22:B28,A33,C22:C28,C32)    
WC      =COUNTIFS(B22:B28,A34,C22:C28,B32)    =COUNTIFS(B22:B28,A34,C22:C28,C32)    
RU      =COUNTIFS(B22:B28,A35,C22:C28,B32)    =COUNTIFS(B22:B28,A35,C22:C28,C32)

Try this method. COUNTIFs will work.

Upvotes: 1

Related Questions