Hi.

I have a table of entries, structured in the following way:

Column A contains dates. Column B contains a data validated list. Column D
contains values.

I now wish to go through all the rows in the list, and sum the values if 1)
the value of the cell in column B matches "criteria", and 2) the date in
column A is in a specific month.

I tried the following formula to sum over all dates in April, without success:

=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")

I suspect my use of the MONTH function is a bit unorthodox, but can't seem
to figure out the proper way to do it.

I would greatly appreciate any help!

/Jonas

=SUMPRODUCT(--(MONTH(A1:A10)=4);--(B1:B10="criteria");D1:D10)

--
---
HTH

Bob


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



"Jonas" wrote in message
...
Hi.

I have a table of entries, structured in the following way:

Column A contains dates. Column B contains a data validated list. Column D
contains values.

I now wish to go through all the rows in the list, and sum the values if
1)
the value of the cell in column B matches "criteria", and 2) the date in
column A is in a specific month.

I tried the following formula to sum over all dates in April, without
success:

=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")

I suspect my use of the MONTH function is a bit unorthodox, but can't seem
to figure out the proper way to do it.

I would greatly appreciate any help!

/Jonas

MONTH=4? MONTH of what?
Did you look in Help for the usage of MONTH?

Tyro

"Jonas" wrote in message
...
Hi.

I have a table of entries, structured in the following way:

Column A contains dates. Column B contains a data validated list. Column D
contains values.

I now wish to go through all the rows in the list, and sum the values if
1)
the value of the cell in column B matches "criteria", and 2) the date in
column A is in a specific month.

I tried the following formula to sum over all dates in April, without
success:

=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")

I suspect my use of the MONTH function is a bit unorthodox, but can't seem
to figure out the proper way to do it.

I would greatly appreciate any help!

/Jonas

I think Bob's formulas are supposed to contain commas, not semicolons, as such:
=SUMPRODUCT(--(MONTH(A1:A10)=4),--(B1:B10="criteria"),D1:D10)

Also, you may consider this:
=COUNTIF(A1:A9,"=4")+COUNTIF(B1:B9,"criteria")

However, that may not yield the results you are after.

Finally, a pivot table would do it for you, but I think you'd have to add a
helper column, and you have to use something like the =month() function.


Regards,
Ryan---
--
RyGuy


"Tyro" wrote:

MONTH=4? MONTH of what?
Did you look in Help for the usage of MONTH?

Tyro

"Jonas" wrote in message
...
Hi.

I have a table of entries, structured in the following way:

Column A contains dates. Column B contains a data validated list. Column D
contains values.

I now wish to go through all the rows in the list, and sum the values if
1)
the value of the cell in column B matches "criteria", and 2) the date in
column A is in a specific month.

I tried the following formula to sum over all dates in April, without
success:

=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")

I suspect my use of the MONTH function is a bit unorthodox, but can't seem
to figure out the proper way to do it.

I would greatly appreciate any help!

/Jonas

No they are meant to contain semi-colons, as did the OPs, because presumably
he has a continental Excel.

--
---
HTH

Bob


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



"ryguy7272" wrote in message
...
I think Bob's formulas are supposed to contain commas, not semicolons, as
such:
=SUMPRODUCT(--(MONTH(A1:A10)=4),--(B1:B10="criteria"),D1:D10)

Also, you may consider this:
=COUNTIF(A1:A9,"=4")+COUNTIF(B1:B9,"criteria")

However, that may not yield the results you are after.

Finally, a pivot table would do it for you, but I think you'd have to add
a
helper column, and you have to use something like the =month() function.


Regards,
Ryan---
--
RyGuy


"Tyro" wrote:

MONTH=4? MONTH of what?
Did you look in Help for the usage of MONTH?

Tyro

"Jonas" wrote in message
...
Hi.

I have a table of entries, structured in the following way:

Column A contains dates. Column B contains a data validated list.
Column D
contains values.

I now wish to go through all the rows in the list, and sum the values
if
1)
the value of the cell in column B matches "criteria", and 2) the date
in
column A is in a specific month.

I tried the following formula to sum over all dates in April, without
success:

=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")

I suspect my use of the MONTH function is a bit unorthodox, but can't
seem
to figure out the proper way to do it.

I would greatly appreciate any help!

/Jonas

True, but semi-colons are used for commas. To my understanding, they do not
replace the need for arguments enclosed in parentheses. MONTH=4 is exactly
what is says it is. It is not MONTH(date).
=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria") does not contain
parentheses to enclose the date argument to the MONTH function, as far as I
can ascertain. Even if the semi-colons were replaced by commas, it is still
MONTH=4. MONTH of what?

Tyro


"Bob Phillips" wrote in message
...
No they are meant to contain semi-colons, as did the OPs, because
presumably he has a continental Excel.

--
---
HTH

Bob


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



"ryguy7272" wrote in message
...
I think Bob's formulas are supposed to contain commas, not semicolons, as
such:
=SUMPRODUCT(--(MONTH(A1:A10)=4),--(B1:B10="criteria"),D1:D10)

Also, you may consider this:
=COUNTIF(A1:A9,"=4")+COUNTIF(B1:B9,"criteria")

However, that may not yield the results you are after.

Finally, a pivot table would do it for you, but I think you'd have to add
a
helper column, and you have to use something like the =month() function.


Regards,
Ryan---
--
RyGuy


"Tyro" wrote:

MONTH=4? MONTH of what?
Did you look in Help for the usage of MONTH?

Tyro

"Jonas" wrote in message
...
Hi.

I have a table of entries, structured in the following way:

Column A contains dates. Column B contains a data validated list.
Column D
contains values.

I now wish to go through all the rows in the list, and sum the values
if
1)
the value of the cell in column B matches "criteria", and 2) the date
in
column A is in a specific month.

I tried the following formula to sum over all dates in April, without
success:

=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")

I suspect my use of the MONTH function is a bit unorthodox, but can't
seem
to figure out the proper way to do it.

I would greatly appreciate any help!

/Jonas

Where did Bob use month without arguments?


--


Regards,


Peo Sjoblom


"Tyro" wrote in message
...
True, but semi-colons are used for commas. To my understanding, they do
not replace the need for arguments enclosed in parentheses. MONTH=4 is
exactly what is says it is. It is not MONTH(date).
=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria") does not contain
parentheses to enclose the date argument to the MONTH function, as far as
I can ascertain. Even if the semi-colons were replaced by commas, it is
still MONTH=4. MONTH of what?

Tyro


"Bob Phillips" wrote in message
...
No they are meant to contain semi-colons, as did the OPs, because
presumably he has a continental Excel.

--
---
HTH

Bob


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



"ryguy7272" wrote in message
...
I think Bob's formulas are supposed to contain commas, not semicolons, as
such:
=SUMPRODUCT(--(MONTH(A1:A10)=4),--(B1:B10="criteria"),D1:D10)

Also, you may consider this:
=COUNTIF(A1:A9,"=4")+COUNTIF(B1:B9,"criteria")

However, that may not yield the results you are after.

Finally, a pivot table would do it for you, but I think you'd have to
add a
helper column, and you have to use something like the =month() function.


Regards,
Ryan---
--
RyGuy


"Tyro" wrote:

MONTH=4? MONTH of what?
Did you look in Help for the usage of MONTH?

Tyro

"Jonas" wrote in message
...
Hi.

I have a table of entries, structured in the following way:

Column A contains dates. Column B contains a data validated list.
Column D
contains values.

I now wish to go through all the rows in the list, and sum the values
if
1)
the value of the cell in column B matches "criteria", and 2) the date
in
column A is in a specific month.

I tried the following formula to sum over all dates in April, without
success:

=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")

I suspect my use of the MONTH function is a bit unorthodox, but can't
seem
to figure out the proper way to do it.

I would greatly appreciate any help!

/Jonas

Jonas did. I misposted. Simply that. And you knew it.

Tyro

"Peo Sjoblom" wrote in message
...
Where did Bob use month without arguments?


--


Regards,


Peo Sjoblom


"Tyro" wrote in message
...
True, but semi-colons are used for commas. To my understanding, they do
not replace the need for arguments enclosed in parentheses. MONTH=4 is
exactly what is says it is. It is not MONTH(date).
=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria") does not contain
parentheses to enclose the date argument to the MONTH function, as far as
I can ascertain. Even if the semi-colons were replaced by commas, it is
still MONTH=4. MONTH of what?

Tyro


"Bob Phillips" wrote in message
...
No they are meant to contain semi-colons, as did the OPs, because
presumably he has a continental Excel.

--
---
HTH

Bob


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



"ryguy7272" wrote in message
...
I think Bob's formulas are supposed to contain commas, not semicolons,
as such:
=SUMPRODUCT(--(MONTH(A1:A10)=4),--(B1:B10="criteria"),D1:D10)

Also, you may consider this:
=COUNTIF(A1:A9,"=4")+COUNTIF(B1:B9,"criteria")

However, that may not yield the results you are after.

Finally, a pivot table would do it for you, but I think you'd have to
add a
helper column, and you have to use something like the =month()
function.


Regards,
Ryan---
--
RyGuy


"Tyro" wrote:

MONTH=4? MONTH of what?
Did you look in Help for the usage of MONTH?

Tyro

"Jonas" wrote in message
...
Hi.

I have a table of entries, structured in the following way:

Column A contains dates. Column B contains a data validated list.
Column D
contains values.

I now wish to go through all the rows in the list, and sum the
values if
1)
the value of the cell in column B matches "criteria", and 2) the
date in
column A is in a specific month.

I tried the following formula to sum over all dates in April,
without
success:

=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")

I suspect my use of the MONTH function is a bit unorthodox, but
can't seem
to figure out the proper way to do it.

I would greatly appreciate any help!

/Jonas

Who are you arguing with, me, the OP, or ryguy7272? You picked up on the OP
(irrelevantly AFAICS), and ryguy7272 picked up on me (incorrectly as it
turns out) but he made the mistake of replying to your posting, not mine in
the thread. My response was to ryguy7272 pointing out that it was correct to
use semi-colons if you have a continental version of Excel, it was not to
you, was not appended to yours. You have managed to mix up three posts,
completely failed to get the gist of the OPs question (I may have also, but
you definitely did), he was trying to show what he had tried but failed to
get working.

And semi-colons are not used for commas, they are used as a separator of
function arguments. You do not use commas, because on the continent, commas
are used as the decimal separator for numbers, so they cannot be used as the
argument separator.

--
---
HTH

Bob


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



"Tyro" wrote in message
...
True, but semi-colons are used for commas. To my understanding, they do
not replace the need for arguments enclosed in parentheses. MONTH=4 is
exactly what is says it is. It is not MONTH(date).
=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria") does not contain
parentheses to enclose the date argument to the MONTH function, as far as
I can ascertain. Even if the semi-colons were replaced by commas, it is
still MONTH=4. MONTH of what?

Tyro


"Bob Phillips" wrote in message
...
No they are meant to contain semi-colons, as did the OPs, because
presumably he has a continental Excel.

--
---
HTH

Bob


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



"ryguy7272" wrote in message
...
I think Bob's formulas are supposed to contain commas, not semicolons, as
such:
=SUMPRODUCT(--(MONTH(A1:A10)=4),--(B1:B10="criteria"),D1:D10)

Also, you may consider this:
=COUNTIF(A1:A9,"=4")+COUNTIF(B1:B9,"criteria")

However, that may not yield the results you are after.

Finally, a pivot table would do it for you, but I think you'd have to
add a
helper column, and you have to use something like the =month() function.


Regards,
Ryan---
--
RyGuy


"Tyro" wrote:

MONTH=4? MONTH of what?
Did you look in Help for the usage of MONTH?

Tyro

"Jonas" wrote in message
...
Hi.

I have a table of entries, structured in the following way:

Column A contains dates. Column B contains a data validated list.
Column D
contains values.

I now wish to go through all the rows in the list, and sum the values
if
1)
the value of the cell in column B matches "criteria", and 2) the date
in
column A is in a specific month.

I tried the following formula to sum over all dates in April, without
success:

=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")

I suspect my use of the MONTH function is a bit unorthodox, but can't
seem
to figure out the proper way to do it.

I would greatly appreciate any help!

/Jonas

If you are addressing me, I am simply stating that the MONTH function
requires a date! As in MONTH(date). Semi-colons and commas are not the
issue. The OP posted =SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria") I
repeat, where is the date for the month function to evaluate??????

Tyro

"Bob Phillips" wrote in message
...
Who are you arguing with, me, the OP, or ryguy7272? You picked up on the
OP (irrelevantly AFAICS), and ryguy7272 picked up on me (incorrectly as it
turns out) but he made the mistake of replying to your posting, not mine
in the thread. My response was to ryguy7272 pointing out that it was
correct to use semi-colons if you have a continental version of Excel, it
was not to you, was not appended to yours. You have managed to mix up
three posts, completely failed to get the gist of the OPs question (I may
have also, but you definitely did), he was trying to show what he had
tried but failed to get working.

And semi-colons are not used for commas, they are used as a separator of
function arguments. You do not use commas, because on the continent,
commas are used as the decimal separator for numbers, so they cannot be
used as the argument separator.

--
---
HTH

Bob


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



"Tyro" wrote in message
...
True, but semi-colons are used for commas. To my understanding, they do
not replace the need for arguments enclosed in parentheses. MONTH=4 is
exactly what is says it is. It is not MONTH(date).
=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria") does not contain
parentheses to enclose the date argument to the MONTH function, as far as
I can ascertain. Even if the semi-colons were replaced by commas, it is
still MONTH=4. MONTH of what?

Tyro


"Bob Phillips" wrote in message
...
No they are meant to contain semi-colons, as did the OPs, because
presumably he has a continental Excel.

--
---
HTH

Bob


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



"ryguy7272" wrote in message
...
I think Bob's formulas are supposed to contain commas, not semicolons,
as such:
=SUMPRODUCT(--(MONTH(A1:A10)=4),--(B1:B10="criteria"),D1:D10)

Also, you may consider this:
=COUNTIF(A1:A9,"=4")+COUNTIF(B1:B9,"criteria")

However, that may not yield the results you are after.

Finally, a pivot table would do it for you, but I think you'd have to
add a
helper column, and you have to use something like the =month()
function.


Regards,
Ryan---
--
RyGuy


"Tyro" wrote:

MONTH=4? MONTH of what?
Did you look in Help for the usage of MONTH?

Tyro

"Jonas" wrote in message
...
Hi.

I have a table of entries, structured in the following way:

Column A contains dates. Column B contains a data validated list.
Column D
contains values.

I now wish to go through all the rows in the list, and sum the
values if
1)
the value of the cell in column B matches "criteria", and 2) the
date in
column A is in a specific month.

I tried the following formula to sum over all dates in April,
without
success:

=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")

I suspect my use of the MONTH function is a bit unorthodox, but
can't seem
to figure out the proper way to do it.

I would greatly appreciate any help!

/Jonas

Hey guys, thanks for all the help.

Sorry about the confusion. Through my example, I was trying to illustrate
what I had tried, but is not working. I tried using the MONTH function
without parenthesis, in hope that the SUMIFS function would apply it to the
range A1:A10.

Basically, what I am trying to do is this:

For each row in the list;
if the date in column A corresponds to the month of April;
and if the value in column B corresponds to 'criteria';
sum the values in column D.

Hope this makes my intentions more clear.

/Jonas

"Tyro" wrote:

If you are addressing me, I am simply stating that the MONTH function
requires a date! As in MONTH(date). Semi-colons and commas are not the
issue. The OP posted =SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria") I
repeat, where is the date for the month function to evaluate??????

Tyro

"Bob Phillips" wrote in message
...
Who are you arguing with, me, the OP, or ryguy7272? You picked up on the
OP (irrelevantly AFAICS), and ryguy7272 picked up on me (incorrectly as it
turns out) but he made the mistake of replying to your posting, not mine
in the thread. My response was to ryguy7272 pointing out that it was
correct to use semi-colons if you have a continental version of Excel, it
was not to you, was not appended to yours. You have managed to mix up
three posts, completely failed to get the gist of the OPs question (I may
have also, but you definitely did), he was trying to show what he had
tried but failed to get working.

And semi-colons are not used for commas, they are used as a separator of
function arguments. You do not use commas, because on the continent,
commas are used as the decimal separator for numbers, so they cannot be
used as the argument separator.

--
---
HTH

Bob


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



"Tyro" wrote in message
...
True, but semi-colons are used for commas. To my understanding, they do
not replace the need for arguments enclosed in parentheses. MONTH=4 is
exactly what is says it is. It is not MONTH(date).
=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria") does not contain
parentheses to enclose the date argument to the MONTH function, as far as
I can ascertain. Even if the semi-colons were replaced by commas, it is
still MONTH=4. MONTH of what?

Tyro


"Bob Phillips" wrote in message
...
No they are meant to contain semi-colons, as did the OPs, because
presumably he has a continental Excel.

--
---
HTH

Bob


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



"ryguy7272" wrote in message
...
I think Bob's formulas are supposed to contain commas, not semicolons,
as such:
=SUMPRODUCT(--(MONTH(A1:A10)=4),--(B1:B10="criteria"),D1:D10)

Also, you may consider this:
=COUNTIF(A1:A9,"=4")+COUNTIF(B1:B9,"criteria")

However, that may not yield the results you are after.

Finally, a pivot table would do it for you, but I think you'd have to
add a
helper column, and you have to use something like the =month()
function.


Regards,
Ryan---
--
RyGuy


"Tyro" wrote:

MONTH=4? MONTH of what?
Did you look in Help for the usage of MONTH?

Tyro

"Jonas" wrote in message
...
Hi.

I have a table of entries, structured in the following way:

Column A contains dates. Column B contains a data validated list.
Column D
contains values.

I now wish to go through all the rows in the list, and sum the
values if
1)
the value of the cell in column B matches "criteria", and 2) the
date in
column A is in a specific month.

I tried the following formula to sum over all dates in April,
without
success:

=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")

I suspect my use of the MONTH function is a bit unorthodox, but
can't seem
to figure out the proper way to do it.

I would greatly appreciate any help!

/Jonas

No ned to apologise, you were perfectly clear in your intent even if you got
the syntax wrong. The confusion was caused elsewhere, not by you.

You can't do it with SUMIFS as I stated earlier. Did you try my suggestion?

--
---
HTH

Bob


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



"Jonas" wrote in message
...
Hey guys, thanks for all the help.

Sorry about the confusion. Through my example, I was trying to illustrate
what I had tried, but is not working. I tried using the MONTH function
without parenthesis, in hope that the SUMIFS function would apply it to
the
range A1:A10.

Basically, what I am trying to do is this:

For each row in the list;
if the date in column A corresponds to the month of April;
and if the value in column B corresponds to 'criteria';
sum the values in column D.

Hope this makes my intentions more clear.

/Jonas

"Tyro" wrote:

If you are addressing me, I am simply stating that the MONTH function
requires a date! As in MONTH(date). Semi-colons and commas are not the
issue. The OP posted =SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria") I
repeat, where is the date for the month function to evaluate??????

Tyro

"Bob Phillips" wrote in message
...
Who are you arguing with, me, the OP, or ryguy7272? You picked up on
the
OP (irrelevantly AFAICS), and ryguy7272 picked up on me (incorrectly as
it
turns out) but he made the mistake of replying to your posting, not
mine
in the thread. My response was to ryguy7272 pointing out that it was
correct to use semi-colons if you have a continental version of Excel,
it
was not to you, was not appended to yours. You have managed to mix up
three posts, completely failed to get the gist of the OPs question (I
may
have also, but you definitely did), he was trying to show what he had
tried but failed to get working.

And semi-colons are not used for commas, they are used as a separator
of
function arguments. You do not use commas, because on the continent,
commas are used as the decimal separator for numbers, so they cannot be
used as the argument separator.

--
---
HTH

Bob


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



"Tyro" wrote in message
...
True, but semi-colons are used for commas. To my understanding, they
do
not replace the need for arguments enclosed in parentheses. MONTH=4 is
exactly what is says it is. It is not MONTH(date).
=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria") does not contain
parentheses to enclose the date argument to the MONTH function, as far
as
I can ascertain. Even if the semi-colons were replaced by commas, it
is
still MONTH=4. MONTH of what?

Tyro


"Bob Phillips" wrote in message
...
No they are meant to contain semi-colons, as did the OPs, because
presumably he has a continental Excel.

--
---
HTH

Bob


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



"ryguy7272" wrote in message
...
I think Bob's formulas are supposed to contain commas, not
semicolons,
as such:
=SUMPRODUCT(--(MONTH(A1:A10)=4),--(B1:B10="criteria"),D1:D10)

Also, you may consider this:
=COUNTIF(A1:A9,"=4")+COUNTIF(B1:B9,"criteria")

However, that may not yield the results you are after.

Finally, a pivot table would do it for you, but I think you'd have
to
add a
helper column, and you have to use something like the =month()
function.


Regards,
Ryan---
--
RyGuy


"Tyro" wrote:

MONTH=4? MONTH of what?
Did you look in Help for the usage of MONTH?

Tyro

"Jonas" wrote in message
...
Hi.

I have a table of entries, structured in the following way:

Column A contains dates. Column B contains a data validated list.
Column D
contains values.

I now wish to go through all the rows in the list, and sum the
values if
1)
the value of the cell in column B matches "criteria", and 2) the
date in
column A is in a specific month.

I tried the following formula to sum over all dates in April,
without
success:

=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")

I suspect my use of the MONTH function is a bit unorthodox, but
can't seem
to figure out the proper way to do it.

I would greatly appreciate any help!

/Jonas

Yes Bob, it works perfectly - thanks a lot!

A question for my own learning, could you briefly explain the logic behind
the formula? What is the purpose behind the use of '--' in the formula?

Thanks again!

/Jonas
---
"Bob Phillips" wrote:

No ned to apologise, you were perfectly clear in your intent even if you got
the syntax wrong. The confusion was caused elsewhere, not by you.

You can't do it with SUMIFS as I stated earlier. Did you try my suggestion?

--
---
HTH

Bob


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



"Jonas" wrote in message
...
Hey guys, thanks for all the help.

Sorry about the confusion. Through my example, I was trying to illustrate
what I had tried, but is not working. I tried using the MONTH function
without parenthesis, in hope that the SUMIFS function would apply it to
the
range A1:A10.

Basically, what I am trying to do is this:

For each row in the list;
if the date in column A corresponds to the month of April;
and if the value in column B corresponds to 'criteria';
sum the values in column D.

Hope this makes my intentions more clear.

/Jonas

"Tyro" wrote:

If you are addressing me, I am simply stating that the MONTH function
requires a date! As in MONTH(date). Semi-colons and commas are not the
issue. The OP posted =SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria") I
repeat, where is the date for the month function to evaluate??????

Tyro

"Bob Phillips" wrote in message
...
Who are you arguing with, me, the OP, or ryguy7272? You picked up on
the
OP (irrelevantly AFAICS), and ryguy7272 picked up on me (incorrectly as
it
turns out) but he made the mistake of replying to your posting, not
mine
in the thread. My response was to ryguy7272 pointing out that it was
correct to use semi-colons if you have a continental version of Excel,
it
was not to you, was not appended to yours. You have managed to mix up
three posts, completely failed to get the gist of the OPs question (I
may
have also, but you definitely did), he was trying to show what he had
tried but failed to get working.

And semi-colons are not used for commas, they are used as a separator
of
function arguments. You do not use commas, because on the continent,
commas are used as the decimal separator for numbers, so they cannot be
used as the argument separator.

--
---
HTH

Bob


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



"Tyro" wrote in message
...
True, but semi-colons are used for commas. To my understanding, they
do
not replace the need for arguments enclosed in parentheses. MONTH=4 is
exactly what is says it is. It is not MONTH(date).
=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria") does not contain
parentheses to enclose the date argument to the MONTH function, as far
as
I can ascertain. Even if the semi-colons were replaced by commas, it
is
still MONTH=4. MONTH of what?

Tyro


"Bob Phillips" wrote in message
...
No they are meant to contain semi-colons, as did the OPs, because
presumably he has a continental Excel.

--
---
HTH

Bob


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



"ryguy7272" wrote in message
...
I think Bob's formulas are supposed to contain commas, not
semicolons,
as such:
=SUMPRODUCT(--(MONTH(A1:A10)=4),--(B1:B10="criteria"),D1:D10)

Also, you may consider this:
=COUNTIF(A1:A9,"=4")+COUNTIF(B1:B9,"criteria")

However, that may not yield the results you are after.

Finally, a pivot table would do it for you, but I think you'd have
to
add a
helper column, and you have to use something like the =month()
function.


Regards,
Ryan---
--
RyGuy


"Tyro" wrote:

MONTH=4? MONTH of what?
Did you look in Help for the usage of MONTH?

Tyro

"Jonas" wrote in message
...
Hi.

I have a table of entries, structured in the following way:

Column A contains dates. Column B contains a data validated list.
Column D
contains values.

I now wish to go through all the rows in the list, and sum the
values if
1)
the value of the cell in column B matches "criteria", and 2) the
date in
column A is in a specific month.

I tried the following formula to sum over all dates in April,
without
success:

=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")

I suspect my use of the MONTH function is a bit unorthodox, but
can't seem
to figure out the proper way to do it.

I would greatly appreciate any help!

/Jonas

I could, but I would just be repeating myself as I have documented it here
http://www.xldynamic.com/source/xld.SUMPRODUCT.html.

--
---
HTH

Bob


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



"Jonas" wrote in message
...
Yes Bob, it works perfectly - thanks a lot!

A question for my own learning, could you briefly explain the logic behind
the formula? What is the purpose behind the use of '--' in the formula?

Thanks again!

/Jonas
---
"Bob Phillips" wrote:

No ned to apologise, you were perfectly clear in your intent even if you
got
the syntax wrong. The confusion was caused elsewhere, not by you.

You can't do it with SUMIFS as I stated earlier. Did you try my
suggestion?

--
---
HTH

Bob


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



"Jonas" wrote in message
...
Hey guys, thanks for all the help.

Sorry about the confusion. Through my example, I was trying to
illustrate
what I had tried, but is not working. I tried using the MONTH function
without parenthesis, in hope that the SUMIFS function would apply it to
the
range A1:A10.

Basically, what I am trying to do is this:

For each row in the list;
if the date in column A corresponds to the month of April;
and if the value in column B corresponds to 'criteria';
sum the values in column D.

Hope this makes my intentions more clear.

/Jonas

"Tyro" wrote:

If you are addressing me, I am simply stating that the MONTH function
requires a date! As in MONTH(date). Semi-colons and commas are not the
issue. The OP posted =SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")
I
repeat, where is the date for the month function to evaluate??????

Tyro

"Bob Phillips" wrote in message
...
Who are you arguing with, me, the OP, or ryguy7272? You picked up on
the
OP (irrelevantly AFAICS), and ryguy7272 picked up on me (incorrectly
as
it
turns out) but he made the mistake of replying to your posting, not
mine
in the thread. My response was to ryguy7272 pointing out that it was
correct to use semi-colons if you have a continental version of
Excel,
it
was not to you, was not appended to yours. You have managed to mix
up
three posts, completely failed to get the gist of the OPs question
(I
may
have also, but you definitely did), he was trying to show what he
had
tried but failed to get working.

And semi-colons are not used for commas, they are used as a
separator
of
function arguments. You do not use commas, because on the continent,
commas are used as the decimal separator for numbers, so they cannot
be
used as the argument separator.

--
---
HTH

Bob


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



"Tyro" wrote in message
...
True, but semi-colons are used for commas. To my understanding,
they
do
not replace the need for arguments enclosed in parentheses. MONTH=4
is
exactly what is says it is. It is not MONTH(date).
=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria") does not contain
parentheses to enclose the date argument to the MONTH function, as
far
as
I can ascertain. Even if the semi-colons were replaced by commas,
it
is
still MONTH=4. MONTH of what?

Tyro


"Bob Phillips" wrote in message
...
No they are meant to contain semi-colons, as did the OPs, because
presumably he has a continental Excel.

--
---
HTH

Bob


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



"ryguy7272" wrote in message
...
I think Bob's formulas are supposed to contain commas, not
semicolons,
as such:
=SUMPRODUCT(--(MONTH(A1:A10)=4),--(B1:B10="criteria"),D1:D10)

Also, you may consider this:
=COUNTIF(A1:A9,"=4")+COUNTIF(B1:B9,"criteria")

However, that may not yield the results you are after.

Finally, a pivot table would do it for you, but I think you'd
have
to
add a
helper column, and you have to use something like the =month()
function.


Regards,
Ryan---
--
RyGuy


"Tyro" wrote:

MONTH=4? MONTH of what?
Did you look in Help for the usage of MONTH?

Tyro

"Jonas" wrote in message
...
Hi.

I have a table of entries, structured in the following way:

Column A contains dates. Column B contains a data validated
list.
Column D
contains values.

I now wish to go through all the rows in the list, and sum the
values if
1)
the value of the cell in column B matches "criteria", and 2)
the
date in
column A is in a specific month.

I tried the following formula to sum over all dates in April,
without
success:

=SUMIFS(D1:D10;A1:A10;MONTH=4;B1:B10;"criteria")

I suspect my use of the MONTH function is a bit unorthodox,
but
can't seem
to figure out the proper way to do it.

I would greatly appreciate any help!

/Jonas