SUMPROD question
 

Hi,

I am trying to calculate the number of arrivals for the month of August on a
spreadsheet. The month appears in column E, the date in column F and I have
FALSE for departures and TRUE for arrivals in column G.

I have tried:
=SUMPROD(--TEXT($F$3:$F$276; "aaamm")="200708),--($G$3:$G$276=TRUE)
I have also tried
=SUMPROD(--TEXT($E$3:$E$276; "August")),--($G$3:$G$276=TRUE)

both formulas give me 0 when I should be getting 23

Any help would be greatly appreciated

Thanks
Jeannie


but I get 0 as a result when I should be getting 21

SUMPROD question
 

=SUMPRODUCT(--($E$3:$E$276;"August"),--($G$3:$G$276))

or maybe

=SUMPRODUCT(--($E$3:$E$276,"August"),--($G$3:$G$276))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jeannie" wrote in message
...
Hi,

I am trying to calculate the number of arrivals for the month of August on
a
spreadsheet. The month appears in column E, the date in column F and I
have
FALSE for departures and TRUE for arrivals in column G.

I have tried:
=SUMPROD(--TEXT($F$3:$F$276; "aaamm")="200708),--($G$3:$G$276=TRUE)
I have also tried
=SUMPROD(--TEXT($E$3:$E$276; "August")),--($G$3:$G$276=TRUE)

both formulas give me 0 when I should be getting 23

Any help would be greatly appreciated

Thanks
Jeannie


but I get 0 as a result when I should be getting 21

SUMPROD question
 

Try this:

=SUMPRODUCT(--(TEXT($F$3:$F$276, "yyyymm")="200708"),--$G$3:$G$276)


--
Biff
Microsoft Excel MVP


"Jeannie" wrote in message
...
Hi,

I am trying to calculate the number of arrivals for the month of August on
a
spreadsheet. The month appears in column E, the date in column F and I
have
FALSE for departures and TRUE for arrivals in column G.

I have tried:
=SUMPROD(--TEXT($F$3:$F$276; "aaamm")="200708),--($G$3:$G$276=TRUE)
I have also tried
=SUMPROD(--TEXT($E$3:$E$276; "August")),--($G$3:$G$276=TRUE)

both formulas give me 0 when I should be getting 23

Any help would be greatly appreciated

Thanks
Jeannie


but I get 0 as a result when I should be getting 21

SUMPROD question
 

Thanks, that last formula is actually the one I use for each month, however,
for some reason, it refuses to work for August, September, October, November
and December, it always gives me 0. I copied the format from the last day in
July, changed the date to the August date, then recopied that date on the
other rows for about 10 rows, and it still gives me 0. Which is why I
decided to add a column with the month spelled out. Any ideas?

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(TEXT($F$3:$F$276, "yyyymm")="200708"),--$G$3:$G$276)


--
Biff
Microsoft Excel MVP


"Jeannie" wrote in message
...
Hi,

I am trying to calculate the number of arrivals for the month of August on
a
spreadsheet. The month appears in column E, the date in column F and I
have
FALSE for departures and TRUE for arrivals in column G.

I have tried:
=SUMPROD(--TEXT($F$3:$F$276; "aaamm")="200708),--($G$3:$G$276=TRUE)
I have also tried
=SUMPROD(--TEXT($E$3:$E$276; "August")),--($G$3:$G$276=TRUE)

both formulas give me 0 when I should be getting 23

Any help would be greatly appreciated

Thanks
Jeannie


but I get 0 as a result when I should be getting 21

SUMPROD question
 

Sorry, I guess I had a really "blonde moment". I just realized that the
formula that worked so well from January to July was actually copied from
another worksheet, which only had 276. This other worksheet that keeps
giving me 0 for August to December has 576 rows.
Now, I have a new problem, if I try to change the range from $F$3:F4F276 to
$F$3:$F$576 I get #VALUE, even for the formulas that worked previously
(January to July).
Any further help would be greatly appreciated
Thanks
Jeanne

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(TEXT($F$3:$F$276, "yyyymm")="200708"),--$G$3:$G$276)


--
Biff
Microsoft Excel MVP


"Jeannie" wrote in message
...
Hi,

I am trying to calculate the number of arrivals for the month of August on
a
spreadsheet. The month appears in column E, the date in column F and I
have
FALSE for departures and TRUE for arrivals in column G.

I have tried:
=SUMPROD(--TEXT($F$3:$F$276; "aaamm")="200708),--($G$3:$G$276=TRUE)
I have also tried
=SUMPROD(--TEXT($E$3:$E$276; "August")),--($G$3:$G$276=TRUE)

both formulas give me 0 when I should be getting 23

Any help would be greatly appreciated

Thanks
Jeannie


but I get 0 as a result when I should be getting 21

SUMPROD question
 

Sorry about all this, the command did in fact work, I managed it
Thanks for your help
Jeannie

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(TEXT($F$3:$F$276, "yyyymm")="200708"),--$G$3:$G$276)


--
Biff
Microsoft Excel MVP


"Jeannie" wrote in message
...
Hi,

I am trying to calculate the number of arrivals for the month of August on
a
spreadsheet. The month appears in column E, the date in column F and I
have
FALSE for departures and TRUE for arrivals in column G.

I have tried:
=SUMPROD(--TEXT($F$3:$F$276; "aaamm")="200708),--($G$3:$G$276=TRUE)
I have also tried
=SUMPROD(--TEXT($E$3:$E$276; "August")),--($G$3:$G$276=TRUE)

both formulas give me 0 when I should be getting 23

Any help would be greatly appreciated

Thanks
Jeannie


but I get 0 as a result when I should be getting 21

SUMPROD question
 

I stepped out for a while...

Ok, glad to hear you got it straightened out. Thanks for letting us know.

--
Biff
Microsoft Excel MVP


"Jeannie" wrote in message
...
Sorry about all this, the command did in fact work, I managed it
Thanks for your help
Jeannie

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(TEXT($F$3:$F$276, "yyyymm")="200708"),--$G$3:$G$276)


--
Biff
Microsoft Excel MVP


"Jeannie" wrote in message
...
Hi,

I am trying to calculate the number of arrivals for the month of August
on
a
spreadsheet. The month appears in column E, the date in column F and I
have
FALSE for departures and TRUE for arrivals in column G.

I have tried:
=SUMPROD(--TEXT($F$3:$F$276; "aaamm")="200708),--($G$3:$G$276=TRUE)
I have also tried
=SUMPROD(--TEXT($E$3:$E$276; "August")),--($G$3:$G$276=TRUE)

both formulas give me 0 when I should be getting 23

Any help would be greatly appreciated

Thanks
Jeannie


but I get 0 as a result when I should be getting 21