Help WITH SUMPRODUCT

[PhBarreto]

Hey guys!

I would like to create a formula that counts the unique values in column G of the TB_incident_task tab with the formula below, which is my current formula.

=SUMPRODUCT((TB_incident_task!$E$2:$E$8571=Dashboard!C14) * (TB_incident_task!$K$2:$K$8571=Dashboard!$B$5))

I tried to create it in the following way (and a thousand other ways :D), but it returns the value 1 which is incorrect, because I have 3 records. I checked using the filters to validate if the formula below is correct.

=SUMPRODUCT((TB_incident_task!$E$2:$E$8571=Painel!C13) * (TB_incident_task!$K$2:$K$8571=Painel!$B$5) / (COUNTIF(TB_incident_task!$G$2:$G$8571, TB_incident_task!$G$2:$G$8571) + (TB_incident_task!$G$2:$G$8571="")))

Could you help me?
Thank you in advance!

 

[Fluff]

How about

=ROWS(UNIQUE(FILTER(TB_incident_task!$G$2:$G$8571,(TB_incident_task!$E$2:$E$8571=C14)*(TB_incident_task!$K$2:$K$8571=$B$5))))

 

[PhBarreto]

How about

=ROWS(UNIQUE(FILTER(TB_incident_task!$G$2:$G$8571,(TB_incident_task!$E$2:$E$8571=C14)*(TB_incident_task!$K$2:$K$8571=$B$5))))

Thanks Fluff!

It worked great!

I tried to check the values using the formula below, applying the concept you indicated. When adding the result of each row, I got a sum of 1639 unique items. However, by following the concept applied in the formula, I believe I made a mistake, as the result returned was 1444. What am I doing wrong?

=IFERROR(ROWS(UNIQUE(FILTER(TB_incident_task!$G$2:$G$8571, (TB_incident_task!$K$2:$K$8571=$B$5)))); 0)

 

[PhBarreto]

Thanks Fluff!

It worked great!

I tried to check the values using the formula below, applying the concept you indicated. When adding the result of each row, I got a sum of 1639 unique items. However, by following the concept applied in the formula, I believe I made a mistake, as the result returned was 1444. What am I doing wrong?

=IFERROR(ROWS(UNIQUE(FILTER(TB_incident_task!$G$2:$G$8571, (TB_incident_task!$K$2:$K$8571=$B$5)))); 0)

One more piece of information:

The value 1444 is correct. It contains the data without repetition from column G. In column K, the values are repeated and the result is 1639.

 

[Fluff]

If the correct value is 1444 & that is what the formula returned, what's the problem?

 

[PhBarreto]

If the correct value is 1444 & that is what the formula returned, what's the problem?

I used the formula below in the range C12:C53, and the sum is 1639. However, it should be 1444.

=IFERROR(ROWS(UNIQUE(FILTER(TB_incident_task!$G$2:$G$8571, (TB_incident_task!$E$2:$E$8571=C12)*(TB_incident_task!$K$2:$K$8571=$B$5)))), 0)

 

[Fluff]

That sounds as though of the values might not be exactly the same. Are they text or numbers?

 

[PhBarreto]

That sounds as though of the values might not be exactly the same. Are they text or numbers?

They are text.


Examples:
Column E - Name1, Name2, Name3, Name4, Name5 etc. (TB_incident_task!$E$2:$E$8571=C12)
Column G - ABC123456 (TB_incident_task!$G$2:$G$8571 - should only count unique data from that column)
Column K - Info about incident (TB_incident_task!$K$2:$K$8571=$B$5)

 

[Fluff]

Check that none of those columns have any leading/trailing spaces and/or typos

 

[PhBarreto]

Check that none of those columns have any leading/trailing spaces and/or typos

Tks Fluff! It worked great!