formula help
 

Hi,

I need your help please. I have a need to calculate a sum of numbers in one
range if criteria are met from two other ranges. I have this formula that
calculates the correct sum for one critera, =SUMIF(B12:B399,"=PI",E12:E399),
now I need to add the second criteria. The second range is a date format. I
need my result to be monthly sums where the data in the second criteria range
are days entered in date format from all twelve months.
--
Jerry

formula help
 

Would be nice to know which column the dates are in

"Jerry Rogers" wrote:

Hi,

I need your help please. I have a need to calculate a sum of numbers in one
range if criteria are met from two other ranges. I have this formula that
calculates the correct sum for one critera, =SUMIF(B12:B399,"=PI",E12:E399),
now I need to add the second criteria. The second range is a date format. I
need my result to be monthly sums where the data in the second criteria range
are days entered in date format from all twelve months.
--
Jerry

formula help
 

If column C is not the column for dates then change C12:C399 to what you need
and C1 will be were you enter a date to test for sum of dates
=SUMPRODUCT((B12:B399="PI")*(C12:C399=C1)*(E12:E39 9))

"Jerry Rogers" wrote:

Hi,

I need your help please. I have a need to calculate a sum of numbers in one
range if criteria are met from two other ranges. I have this formula that
calculates the correct sum for one critera, =SUMIF(B12:B399,"=PI",E12:E399),
now I need to add the second criteria. The second range is a date format. I
need my result to be monthly sums where the data in the second criteria range
are days entered in date format from all twelve months.
--
Jerry

formula help
 

Hi Mike,

Thanks for the correction.

The first selection criteria is in column B, the dates are in the A column;
(A12:A399), and the numbers to be summed are in the E Column; (E12:E399).


--
Jerry


"Mike" wrote:

Would be nice to know which column the dates are in

"Jerry Rogers" wrote:

Hi,

I need your help please. I have a need to calculate a sum of numbers in one
range if criteria are met from two other ranges. I have this formula that
calculates the correct sum for one critera, =SUMIF(B12:B399,"=PI",E12:E399),
now I need to add the second criteria. The second range is a date format. I
need my result to be monthly sums where the data in the second criteria range
are days entered in date format from all twelve months.
--
Jerry

formula help
 

A1 enter a date to test for sum of date
=SUMPRODUCT((B12:B399="PI")*(A12:A399=A1)*(E12:E39 9))

"Jerry Rogers" wrote:

Hi Mike,

Thanks for the correction.

The first selection criteria is in column B, the dates are in the A column;
(A12:A399), and the numbers to be summed are in the E Column; (E12:E399).


--
Jerry


"Mike" wrote:

Would be nice to know which column the dates are in

"Jerry Rogers" wrote:

Hi,

I need your help please. I have a need to calculate a sum of numbers in one
range if criteria are met from two other ranges. I have this formula that
calculates the correct sum for one critera, =SUMIF(B12:B399,"=PI",E12:E399),
now I need to add the second criteria. The second range is a date format. I
need my result to be monthly sums where the data in the second criteria range
are days entered in date format from all twelve months.
--
Jerry

formula help
 

Hi again Mike,

Thanks for the help so far. I have one more little problem with my formula
that I'm sure you can help fix. My dates entered into the date column are
days of the month, ie 3 SEP 06, 6 SEP 06, 12 OCT 06, etc. I need the formula
to sum the numbers in column E for all the dates in column A that fall in the
month of SEP 06 excluding all other date entries, but only if column B = "PI".

Can you help me with this addition?
--
Jerry


"Mike" wrote:

If column C is not the column for dates then change C12:C399 to what you need
and C1 will be were you enter a date to test for sum of dates
=SUMPRODUCT((B12:B399="PI")*(C12:C399=C1)*(E12:E39 9))

"Jerry Rogers" wrote:

Hi,

I need your help please. I have a need to calculate a sum of numbers in one
range if criteria are met from two other ranges. I have this formula that
calculates the correct sum for one critera, =SUMIF(B12:B399,"=PI",E12:E399),
now I need to add the second criteria. The second range is a date format. I
need my result to be monthly sums where the data in the second criteria range
are days entered in date format from all twelve months.
--
Jerry

formula help
 

C2 is for ending date
=SUMPRODUCT(--(B12:B399="PI")*(A12:A399=C1)*(A12:A399<=C2)*(E12 :E399))

"Jerry Rogers" wrote:

Hi again Mike,

Thanks for the help so far. I have one more little problem with my formula
that I'm sure you can help fix. My dates entered into the date column are
days of the month, ie 3 SEP 06, 6 SEP 06, 12 OCT 06, etc. I need the formula
to sum the numbers in column E for all the dates in column A that fall in the
month of SEP 06 excluding all other date entries, but only if column B = "PI".

Can you help me with this addition?
--
Jerry


"Mike" wrote:

If column C is not the column for dates then change C12:C399 to what you need
and C1 will be were you enter a date to test for sum of dates
=SUMPRODUCT((B12:B399="PI")*(C12:C399=C1)*(E12:E39 9))

"Jerry Rogers" wrote:

Hi,

I need your help please. I have a need to calculate a sum of numbers in one
range if criteria are met from two other ranges. I have this formula that
calculates the correct sum for one critera, =SUMIF(B12:B399,"=PI",E12:E399),
now I need to add the second criteria. The second range is a date format. I
need my result to be monthly sums where the data in the second criteria range
are days entered in date format from all twelve months.
--
Jerry

formula help
 

this will sum month 9 (assumes col a has proper dates)
=SUMPRODUCT((month(a12:a399)=9)*(B12:B399="PI")*E1 2:E399)


--
Don Guillett
SalesAid Software

"Jerry Rogers" wrote in message
...
Hi again Mike,

Thanks for the help so far. I have one more little problem with my
formula
that I'm sure you can help fix. My dates entered into the date column are
days of the month, ie 3 SEP 06, 6 SEP 06, 12 OCT 06, etc. I need the
formula
to sum the numbers in column E for all the dates in column A that fall in
the
month of SEP 06 excluding all other date entries, but only if column B =
"PI".

Can you help me with this addition?
--
Jerry


"Mike" wrote:

If column C is not the column for dates then change C12:C399 to what you
need
and C1 will be were you enter a date to test for sum of dates
=SUMPRODUCT((B12:B399="PI")*(C12:C399=C1)*(E12:E39 9))

"Jerry Rogers" wrote:

Hi,

I need your help please. I have a need to calculate a sum of numbers
in one
range if criteria are met from two other ranges. I have this formula
that
calculates the correct sum for one critera,
=SUMIF(B12:B399,"=PI",E12:E399),
now I need to add the second criteria. The second range is a date
format. I
need my result to be monthly sums where the data in the second criteria
range
are days entered in date format from all twelve months.
--
Jerry

formula help
 

How would you get proper dates ?

"Don Guillett" wrote:

this will sum month 9 (assumes col a has proper dates)
=SUMPRODUCT((month(a12:a399)=9)*(B12:B399="PI")*E1 2:E399)


--
Don Guillett
SalesAid Software

"Jerry Rogers" wrote in message
...
Hi again Mike,

Thanks for the help so far. I have one more little problem with my
formula
that I'm sure you can help fix. My dates entered into the date column are
days of the month, ie 3 SEP 06, 6 SEP 06, 12 OCT 06, etc. I need the
formula
to sum the numbers in column E for all the dates in column A that fall in
the
month of SEP 06 excluding all other date entries, but only if column B =
"PI".

Can you help me with this addition?
--
Jerry


"Mike" wrote:

If column C is not the column for dates then change C12:C399 to what you
need
and C1 will be were you enter a date to test for sum of dates
=SUMPRODUCT((B12:B399="PI")*(C12:C399=C1)*(E12:E39 9))

"Jerry Rogers" wrote:

Hi,

I need your help please. I have a need to calculate a sum of numbers
in one
range if criteria are met from two other ranges. I have this formula
that
calculates the correct sum for one critera,
=SUMIF(B12:B399,"=PI",E12:E399),
now I need to add the second criteria. The second range is a date
format. I
need my result to be monthly sums where the data in the second criteria
range
are days entered in date format from all twelve months.
--
Jerry

formula help
 

When I said proper dates I meant dates instead of text.

--
Don Guillett
SalesAid Software

"Mike" wrote in message
...
How would you get proper dates ?

"Don Guillett" wrote:

this will sum month 9 (assumes col a has proper dates)
=SUMPRODUCT((month(a12:a399)=9)*(B12:B399="PI")*E1 2:E399)


--
Don Guillett
SalesAid Software

"Jerry Rogers" wrote in message
...
Hi again Mike,

Thanks for the help so far. I have one more little problem with my
formula
that I'm sure you can help fix. My dates entered into the date column
are
days of the month, ie 3 SEP 06, 6 SEP 06, 12 OCT 06, etc. I need the
formula
to sum the numbers in column E for all the dates in column A that fall
in
the
month of SEP 06 excluding all other date entries, but only if column B
=
"PI".

Can you help me with this addition?
--
Jerry


"Mike" wrote:

If column C is not the column for dates then change C12:C399 to what
you
need
and C1 will be were you enter a date to test for sum of dates
=SUMPRODUCT((B12:B399="PI")*(C12:C399=C1)*(E12:E39 9))

"Jerry Rogers" wrote:

Hi,

I need your help please. I have a need to calculate a sum of
numbers
in one
range if criteria are met from two other ranges. I have this
formula
that
calculates the correct sum for one critera,
=SUMIF(B12:B399,"=PI",E12:E399),
now I need to add the second criteria. The second range is a date
format. I
need my result to be monthly sums where the data in the second
criteria
range
are days entered in date format from all twelve months.
--
Jerry