SUMPRODUCT question

[PFAA]

I am trying to count based on multiple conditions. Here is a sample of my
table and the formula.

{=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))}

A B
1188 Feb
1188 Apr
1213 May
1188 May
1259 Mar
1188 Mar
0994 Jun
1188 Jun
0994 Jul
1188 Jun
0993 May
1188 Jun
1188 Feb

It returns a value of "0". Please help.

Thanks,
PFAA

 

[Mike H]

Hi,

It's the quotes, try this

=SUMPRODUCT((A2:A22=1188)*(B2:B22="Feb"))

and if Feb is a correctly formatted date try this
=SUMPRODUCT((A2:A22=1188)*(MONTH(B2:B22)=2))


Putting the number in quotes makes it a text string.

Mike

 

[Boss]

=SUMPRODUCT((A2:A22="1188")*(B2:B22="Feb"))

works fine..
Boss

 

[The Mysterious J]

I use a funky workaround for stuff like this. I write a third (can be hidden)
column in which I combine the 2 values, usually with a carat as a separator,
just in case the combined values could be ambiguous (e.g. 1 & 00 are 100, but
so are 10 & 0).
So... I could count the 1188 & Feb combination by adding column C and then
asking: =COUNTIF(C1:C14,"1188^FEB")
A B C
1188 Feb =A1&"^"&B1
1188 Feb =A2&"^"&B2
1188 Apr =A3&"^"&B3
1213 May =A4&"^"&B4
1188 May =A5&"^"&B5
1259 Mar =A6&"^"&B6
1188 Mar =A7&"^"&B7
0994 Jun =A8&"^"&B8
1188 Jun =A9&"^"&B9
0994 Jul =A10&"^"&B10
1188 Jun =A11&"^"&B11
0993 May =A12&"^"&B12
1188 Jun =A13&"^"&B13
1188 Feb =A14&"^"&B14

 

[PCLIVE]

If column A is using numbers as opposed to text, then you need to remove the
quotes.

=SUMPRODUCT((A2:A22=1188)*(B2:B22="Feb"))

or

=SUMPRODUCT(--(A2:A22=1188),--(B2:B22="Feb"))

HTH,
Paul

 

[PFAA]

Thanks. The first one worked!

So I don't need to use an array? Why do all the instructions I found about
multiple conditions tell me to use an array formula?

Thanks,
PFAA

 

[Mike H]

Hi,

Sumproduct works like an array entered type formula but it doesn't need to
be array entered. I'm glad that worked and thanks for the feedback.

Mike

 

[PFAA]

Me again...sorry.

With regards to you suggestion of adding "MONTH". I would like to be able to
enter dates in this format Mmm dd (i.e. Feb 14 or Mar 2 or Jul 31 etc.). Can
it ready that date format and recognize the month?

Can I do that?

Also, can I enter this formula in Worsheet 2 and ask it calculate the Cells
in Worksheet 1?


Thanks,
PFAA

 

[PFAA]

thanks, when I removed the quotations it worked!

What do the "--" represent? I've seen that in a couple of furmulas and I am
wondering when/why I should use them.

Thanks,
PFAA

 

[PFAA]

Thanks for your response - all I had to do what remove the quotations around
1188 and it worked.

PFAA

 

[PFAA]

Thanks for your reply. But it didn't work until I removed the quotation marks
around 1188....


PFAA

 

[Mike H]

Hi,

With regard to dates it's better to have a properly formatted date than text
and to do that enter a date in the normal way

15/6/2008

and then format it to suit
If you apply a custom format of MMM DD to a correctly formatted date then
you get the result you want which would be JUN 15 for the example above.

Now if you want to do a vlookup on that from another sheet then the formula is
=SUMPRODUCT((Sheet1!A1:A20=1188)*(MONTH(Sheet1!B1:B20)=6))

Yoy could enter the dates as text in the format you want but it looks for an
exact match and the risk of error increases. far better to do it correctly
from the outset.

Mike

 

[PCLIVE]

A double minus is also known as a double urnary.

In this formula: =SUMPRODUCT(--(A1:A10 & D110="1A"))

this section: (A1:A10 & D110="1A")
returns a series of boolean (fancy word for: TRUE/FALSE) values
....which are not numeric to Excel.

When an arithmetic operator (+,-,*,/) is applied to a boolean value,
Excel converts TRUE to 1 and FALSE to 0.

The standard convention is to use
the Double-Minus (--) to convert the values.
It works this way:
TRUE=TRUE
-TRUE = -1
--TRUE = 1

FALSE = FALSE
-FALSE = 0
--FALSE = 0

Now, you could easily use 1*TRUE, but the Dbl-Minus indicates to
knowledgable users that you are forcing a conversion and not
trying to calculate something.

So, In the formula, the TRUE/FALSE values are converted to 1's and 0's
by the "--" and the SUMPRODUCT calculates the total.

- explanation contribution thanks to Ron Coderre

--

 

[PFAA]

Wow. That's quite an answer. =) Thanks. I'm learning more today than I
wanted to.

=)

PFAA