Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Apply Sumprod on mix text & numbers arrays | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions | |||
Another IF question | Excel Worksheet Functions | |||
sum question | Excel Discussion (Misc queries) |