Sumproduct help!

[Pondy43]

=SUMPRODUCT((August!I:I="No notes added")*(August!J:J="John Smith"))
=SUMPRODUCT((August!I:I="Notes added")*(August!J:J="John Smith"))

Hello,

I am trying to implement this formula into my spread sheet. I have multiple names which I need to count 1 if notes have been added or not for stats.

The formula works fine for about four names. However when I copy and paste and change the name to add more it doesn't seem to work and just displays 0. Not a clue why it's not picking them up as it is working fine for the others.

I am not very technically advanced in Excel, so I would appreciate as much detail as possible.

Thanks in advance,

Jack

 

[Jonmo1]

Can you post one that DOESN'T work

FYI, it's best to NOT use entire column references like I:I in sumproduct
Instead, keep it restricted to your actual used area like I1:I1000

Also, try countifs instead of sumproduct.
=COUNTIFS(August!I:I,"No notes added",August!J:J,"John Smith")

 

[Aladin Akyurek]

Try faster...

=COUNTIFS(August!$I:$I,"No notes added",August!$J:$J,$K2)

where K2 houses a name like John Smith.

And, following the same logic...

=COUNTIFS(August!$I:$I,"Notes added",August!$J:$J,$K2)

 

[Pondy43]

The ones that I have posted do not work? I've added John Smith as a test and it doesn't pick them up. When I added the countif fomula it comes up with just 'FALSE'. I need the cell the reflect how many times John Smith hasn't added notes i.e 5.

 

[Pondy43]

Aladin, I'm sorry I do not understand what you mean by the K2 part of the formula. Please could you explain.

 

[AliGW]

Are there trailing spaces in the lookup arrays?

 

[Pondy43]

I am not sure what you mean by trailing spaces or lockup arrays!

 

[AliGW]

Check that there are no blank spaces after the names in column J.

 

[Jonmo1]

The ones that I have posted do not work?

Ok, sorry.
Then post one that DOES work.

Basically I'm trying to establish a baseline...What's different between the one that DOES work, and the one that Doesn't..

 

[Jonmo1]

Aladin, I'm sorry I do not understand what you mean by the K2 part of the formula. Please could you explain.

He's saying put John Smith in K2 (rather than hard coding "John Smith" in the formula)

i.e.
=COUNTIFS(August!$I:$I,"No notes added",August!$J:$J,"John Smith")
If K2 = John Smith, then could do this.
=COUNTIFS(August!$I:$I,"No notes added",August!$J:$J,$K2)