CODEWITHSUNDEEP
excelexcel-formula
Faheera
Faheera

Reputation: 127

How to use Excel formulas to Count?

AIM:

Count:

How the worksheet looks like: 1

Sheet 2(details of the people and the division they are in):

2

Sheet 3: 3

How my formula looks like:

=SUMPRODUCT(COUNTIFS('Sheet3'!$A$3:$A$4747,C1,'Sheet3'!$E$3:$E$4747,"1",'Sheet3'!$C$3:$C$4747,Sheet2!B2:B15))

The above formula does not work. Any help will be appreciated.

Upvotes: 2

Views: 99

Answers (2)

Ames
Ames

Reputation: 470

Based on your logic here:

Count:

ID 1 or ID 2 is from rp

Particular week (eg.week 80)

Count only if the status is successful

Referring to the print screen below for cell address (I simplified the IDs to 10s for easier read)

Formula in cell C3:

=COUNTIFS(I$3:I$10,$C$1,N$3:N$10,1,M$3:M$10,"Successful")

Add in a new column for Sheet3 called ID 1/2 from RP. Formula in cell N2:

=IF(OR(IFNA(INDEX($E$2:$E$7,MATCH(K2,$F$2:$F$7,0))="RP",FALSE),IFNA(INDEX($E$2:$E$7,MATCH(L2,$F$2:$F$7,0))="RP",FALSE)),1,0)

enter image description here

Upvotes: 1

Nicho247
Nicho247

Reputation: 212

Ok, you have to modify your sheets a little bit. We are making your main formula a little simpler.

(1) On sheet2, on column D, set as Division Helper, starting from D2, use formula

=A2

(2) On sheet3, add one new columns after ID2, set as Division 1 and use formula 

=vlookup(C1,Sheet2!B:D,3,false)

(3) On sheet3, add one new column after Division 1, set as Division 2 and use formula

=vlookup(D1,Sheet2!B:D,3,false)

(4) On sheet1, the yellow cell formula should be as follows (assumes 80 is at C1)

=COUNTIFS(Sheet3!$A:$A,$C$1,Sheet3!$E:$E,$A2,Sheet3!$F:$F,B$2,Sheet3!$G:$G,1)"

You should be able to move this formula and it should still work since the references are fixed with $.

Upvotes: 1

Related Questions