• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

count occurrence of a value in one row based on distinct value from another row

jb

jb

Member
Hello Helpers,

Kindly refer attached sample file.
Row 4 contains subject name. Row 5 contains teacher name.

Same Subject name may appear more than once as the subject may be shared by multiple teachers.

Row 6 to row 11 contains student data (there can be more rows of student data). Column B is student id.

Calculation is required to be done in column C.

Rule to calculate column C is:

There are 2 entries of AW for ID 1. Both will be either Y or both must be NA or CAN. (Same for all subjects)
Here it is Y in both column of AW. So it is counted as 1.
Both FWD is Y so counted as 2. IM all 4 is Y. So counted as 1.

This way total for ID 1 is 8.

I am not using excel 365. So suggest me a formula which can be used in excel 2016.
 

Attachments

  • testing.xlsx
    10.2 KB · Views: 6
vletm said:
For me, AW and IM match with Your rules.
Click to expand...
For ID 1 (column B is ID column. ) there are 6 IDs. Calculation is required to be done for all 6 IDs. Row 6 to Row 11.
Column C contains manual calculation for all 6 rows.

Calculation will be done based on Row 4 and Respective ID row ( 6 to 11).
There can be one or more cells in row 4 having same value like AW, IM, FWD. There can be single value like FWD-LAB.
Value in ID row can Y, NA or CAN.
There will be same value in ID row for same value of row 4. For example, for AW in ID row, both the cell will be either Y or both NA or both CAN.
Consider in counting as 1 only if all same value cell in row 4 has corresponding ID row has value Y.

For ex. ID 1,
Both the cell under value AW is Y so it is counted as 1.
Both the cell under value FWD is Y so it is counted as 1.
There is only one cell under value FWD-LAB having value Y so it is counted as 1.
All 4 cell under IM is Y so it is counted as 1. and so on.

Repeat same rule for all other ID row.

I got the formula but there is only one limitation with the formula.
Please refer D column.

I need to mention ending column fixed AI which is last filled cell in row 4 and ID rows as well.

I can have additional entries in future till BI column.

I want to consider E to BI column in formula.
 

Attachments

  • testing.xlsx
    11.3 KB · Views: 4
Last edited:
jb said:
For ID 1 (column B is ID column. ) there are 6 IDs. Calculation is required to be done for all 6 IDs. Row 6 to Row 11.
Column C contains manual calculation for all 6 rows.

Calculation will be done based on Row 4 and Respective ID row ( 6 to 11).
There can be one or more cells in row 4 having same value like AW, IM, FWD. There can be single value like FWD-LAB.
Value in ID row can Y, NA or CAN.
There will be same value in ID row for same value of row 4. For example, for AW in ID row, both the cell will be either Y or both NA or both CAN.
Consider in counting as 1 only if all same value cell in row 4 has corresponding ID row has value Y.

For ex. ID 1,
Both the cell under value AW is Y so it is counted as 1.
Both the cell under value FWD is Y so it is counted as 1.
There is only one cell under value FWD-LAB having value Y so it is counted as 1.
All 4 cell under IM is Y so it is counted as 1. and so on.

Repeat same rule for all other ID row.

I got the formula but there is only one limitation with the formula.
Please refer D column.

I need to mention ending column fixed AI which is last filled cell in row 4 and ID rows as well.

I can have additional entries in future till BI column.

I want to consider E to BI column in formula.
Click to expand...
Got the answer.
Following is general formula

=SUMPRODUCT((1/COUNTIF($E$4:$AM$4,$E$4:$AM$4&""))*($E6:$AM6="Y"))
 
jb said:
Hello Helpers,

Kindly refer attached sample file.
Row 4 contains subject name. Row 5 contains teacher name.

Same Subject name may appear more than once as the subject may be shared by multiple teachers.

Row 6 to row 11 contains student data (there can be more rows of student data). Column B is student id.

Calculation is required to be done in column C.

Rule to calculate column C is:

There are 2 entries of AW for ID 1. Both will be either Y or both must be NA or CAN. (Same for all subjects)
Here it is Y in both column of AW. So it is counted as 1.
Both FWD is Y so counted as 2. IM all 4 is Y. So counted as 1.

This way total for ID 1 is 8.

I am not using excel 365. So suggest me a formula which can be used in excel 2016.
Click to expand...


Per fare questo calcolo in Excel 2016, puoi usare una combinazione di formule come `IF`, `COUNTIFS` e `SUMIFS` per verificare le condizioni che hai descritto. Dato che non posso visualizzare il file, posso solo suggerire un approccio generico:

Puoi creare una formula che verifica se le condizioni per ogni riga sono soddisfatte e poi somma i risultati.

Ad esempio, per contare "Y" o "NA" per una determinata ID studente, puoi usare qualcosa del tipo:

```excel
=IF(AND(AW1="Y", AW2="Y"), 1, 0) + IF(AND(FWD1="Y", FWD2="Y"), 2, 0) + IF(AND(IM1="Y", IM2="Y"), 1, 0)
```

Modifica i riferimenti di cella in base ai tuoi dati. Se ci sono più righe, puoi estendere la formula per sommare i valori su tutte le righe degli studenti.

Se il file fosse visibile, sarebbe più facile darti una soluzione esatta.
 
Not sure if you already have your answer but try in cell D6:
Code: 
=SUMPRODUCT(($E$4:$BI$4<>"")/COUNTIF($E$4:$BI$4,$E$4:$BI$4&"")*($E6:$BI6="Y"))
and copy down. Test thoroughly; I haven't.
 
You must log in or register to reply here.
Back
Top