how to setup formula?

A

A. Toczko

Hello. This is what I want to do:

I want it to look at two columns in a spreadsheet. If Column S=Jane, I want
it to count each time Column D='apple'. So it should only be looking at the
rows where S=Jane. How can this be done? Thanks.
 
A

A. Toczko

If doesn't accept the formula. Where have I gone wrong?

=SUMPRODUCT(--('Data!'$S$2:$S$2000="Jane"),--('Data!'$D$2:$D$2000="apple"))
 
D

Dave Peterson

Try removing the single quotes around Data:
=SUMPRODUCT(--(Data!$S$2:$S$2000="Jane"),--(Data!$D$2:$D$2000="apple"))

Or move them
=SUMPRODUCT(--('Data'!$S$2:$S$2000="Jane"),--('Data'!$D$2:$D$2000="apple"))

(excel will remove them if you don't need them--and a worksheet named Data won't
need them.
 
B

Biff

Hi!

Put the apostrophe on the other side of the exclaimation point:

'Data'!

You don't need them unless the sheet name contains spaces.

Sheet name = Data = not needed = Data!
Sheet name = Data 1 = needed = 'Data 1'!

Biff
 
A

A. Toczko

Thanks! I tried removing the apostrophe and it worked. I'll also remember
the placement for other uses as well.
 
Top