Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SumProduct or CountIf
I am trying to implement the following function to count the number of
entries I have for January, February, etc. =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")) It works fine for February, March, etc. but not for January because it reads the empty cells as being 01/01/1901. Any suggestions? Thanks. |
#2
|
|||
|
|||
Hi Kim Try =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")*(SheetNam e!J5:J8,<)) -- Regards Roger Govier "Kim" wrote in message ... I am trying to implement the following function to count the number of entries I have for January, February, etc. =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")) It works fine for February, March, etc. but not for January because it reads the empty cells as being 01/01/1901. Any suggestions? Thanks. |
#3
|
|||
|
|||
Thank you. I could not get this to work. Excel tells me my function
contains an error. "Roger Govier" wrote: Hi Kim Try =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")*(SheetNam e!J5:J8,<)) -- Regards Roger Govier "Kim" wrote in message ... I am trying to implement the following function to count the number of entries I have for January, February, etc. =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")) It works fine for February, March, etc. but not for January because it reads the empty cells as being 01/01/1901. Any suggestions? Thanks. |
#4
|
|||
|
|||
That gives me an error. Perhaps something like this, instead:
=SUMPRODUCT(--(MONTH(SheetName!J5:J8)=1),--(SheetName!J5:J8<"")) Note that if you're going to do the array multiplication first, using *, there's no reason to use the double unary minuses (--). If you're going to let SUMPRODUCT to the array multiplication by using the comma notation, the double unary minuses are needed. The latter is slightly more efficient. In article , "Roger Govier" wrote: Hi Kim Try =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")*(SheetNam e!J5:J8,<)) |
#5
|
|||
|
|||
Correction:
With multiple terms, one only need use at least one unary minus per conditional, as long as the total number of unary minuses is even. I find it's easier just to use double unary minuses for each conditional all the time, if only to avoid thinking/explaining. In article , JE McGimpsey wrote: If you're going to let SUMPRODUCT to the array multiplication by using the comma notation, the double unary minuses are needed. The latter is slightly more efficient. |
#6
|
|||
|
|||
Thank you. I got it to work.
=SUMPRODUCT(--(TEXT(SheetName!K4:K800, "mmmm")="January"), --(Countrywide!K4:K800<0)) "JE McGimpsey" wrote: That gives me an error. Perhaps something like this, instead: =SUMPRODUCT(--(MONTH(SheetName!J5:J8)=1),--(SheetName!J5:J8<"")) Note that if you're going to do the array multiplication first, using *, there's no reason to use the double unary minuses (--). If you're going to let SUMPRODUCT to the array multiplication by using the comma notation, the double unary minuses are needed. The latter is slightly more efficient. In article , "Roger Govier" wrote: Hi Kim Try =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")*(SheetNam e!J5:J8,<)) |
#7
|
|||
|
|||
You may want to add that --(SheetName!K4:K800<"") portion into your formula.
Try clearing the contents on sheetname!K4. An empty cell will look like January when you do: =text(a1,"mmmm") Kim wrote: Thank you. I got it to work. =SUMPRODUCT(--(TEXT(SheetName!K4:K800, "mmmm")="January"), --(Countrywide!K4:K800<0)) "JE McGimpsey" wrote: That gives me an error. Perhaps something like this, instead: =SUMPRODUCT(--(MONTH(SheetName!J5:J8)=1),--(SheetName!J5:J8<"")) Note that if you're going to do the array multiplication first, using *, there's no reason to use the double unary minuses (--). If you're going to let SUMPRODUCT to the array multiplication by using the comma notation, the double unary minuses are needed. The latter is slightly more efficient. In article , "Roger Govier" wrote: Hi Kim Try =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")*(SheetNam e!J5:J8,<)) -- Dave Peterson |
#8
|
|||
|
|||
JFYI: this formula won't work in any other version of the Office, but
English. If your application may be used internationally you're better off using the formula proposed by JE McGimpsey Regards, KL "Kim" wrote in message ... Thank you. I got it to work. =SUMPRODUCT(--(TEXT(SheetName!K4:K800, "mmmm")="January"), --(Countrywide!K4:K800<0)) "JE McGimpsey" wrote: That gives me an error. Perhaps something like this, instead: =SUMPRODUCT(--(MONTH(SheetName!J5:J8)=1),--(SheetName!J5:J8<"")) Note that if you're going to do the array multiplication first, using *, there's no reason to use the double unary minuses (--). If you're going to let SUMPRODUCT to the array multiplication by using the comma notation, the double unary minuses are needed. The latter is slightly more efficient. In article , "Roger Govier" wrote: Hi Kim Try =SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")*(SheetNam e!J5:J8,<)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif, sumproduct | New Users to Excel | |||
COUNTIF or SUMPRODUCT counting multiple criteria | Excel Worksheet Functions | |||
COUNTIF or SUMPRODUCT counting multiple criteria | Excel Worksheet Functions | |||
Using COUNTIF with 2 criteria - SUMPRODUCT? | Excel Worksheet Functions | |||
SUMPRODUCT & COUNTIF | Excel Worksheet Functions |