formula help

[Guest]

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.

 

[Guest]

Would be nice to know which column the dates are in

 

[Guest]

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:E399))

 

[Guest]

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).

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

How would you get proper dates ?

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

 

[Don Guillett]

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