help - sumproduct within text range-
 

Hi there,

I need your help to solve sumproduct formule for the Area column :

Target Status Date
=== ===============================
jan,feb(march 06) done 1 mar 06
March 06 done 20 feb 06
dec 05,jan,feb,march(april 06) done 1 apr 06
feb 06 cancel 28 feb 06
feb (March 06) ongoing 15 march 06
feb 06 done 7 feb 06

I want to sum all target grouped in "Feb" which has been "done".
=SUMPRODUCT((D4:D9="*feb*")*(E4:E15="done"))

the result is : 0 ( i assumed that the sumproduct can not identify *
(asterisk) as i tought -- * = any charachter)

the correct one should be : 3

Please help me to solve this problem. I have number of column and hundreds
row needed to be sum with multiple condition of formula such above formula
(summing wihtin 2 condition)

Thank you
Firman
Ericsson Indonesia

help - sumproduct within text range-
 

=SUMPRODUCT((D4:D9="*feb*")*(E4:E15="done"))

Try this slight revision to your formula above:
=SUMPRODUCT((MONTH(D4:D9)=2)*(TRIM(E4:E9)="done"))

Real dates are assumed in D4:D9,
E4:E9 is assumed to house the status: done, ongoing, etc

Range in col E is corrected to E4:E9,
with an optional TRIM(..) thrown in for added robustness
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Firman-EID" wrote in message
...
Hi there,

I need your help to solve sumproduct formule for the Area column :

Target Status Date
=== ===============================
jan,feb(march 06) done 1 mar 06
March 06 done 20 feb 06
dec 05,jan,feb,march(april 06) done 1 apr 06
feb 06 cancel 28 feb 06
feb (March 06) ongoing 15 march 06
feb 06 done 7 feb 06

I want to sum all target grouped in "Feb" which has been "done".
=SUMPRODUCT((D4:D9="*feb*")*(E4:E15="done"))

the result is : 0 ( i assumed that the sumproduct can not identify *
(asterisk) as i tought -- * = any charachter)

the correct one should be : 3

Please help me to solve this problem. I have number of column and hundreds
row needed to be sum with multiple condition of formula such above formula
(summing wihtin 2 condition)

Thank you
Firman
Ericsson Indonesia

help - sumproduct within text range-
 

Hi!

Try this:

This assumes that your "Target" column is ALL TEXT. You have entries like:
March 06 and feb 06 . If I enter those values Excel AUTOMATICALLY formats
them as DATES which are in realitly, numbers.

=SUMPRODUCT(--(ISNUMBER(SEARCH("Feb",D4:D9))),--(E4:E9="Done"))

=SUMPRODUCT((D4:D9="*feb*")*(E4:E15="done"))

The arrays have to be EXACTLY the same size:

D4:D9 and E4:E15 are not the same size.

Biff

"Firman-EID" wrote in message
...
Hi there,

I need your help to solve sumproduct formule for the Area column :

Target Status Date
=== ===============================
jan,feb(march 06) done 1 mar 06
March 06 done 20 feb 06
dec 05,jan,feb,march(april 06) done 1 apr 06
feb 06 cancel 28 feb 06
feb (March 06) ongoing 15 march 06
feb 06 done 7 feb 06

I want to sum all target grouped in "Feb" which has been "done".
=SUMPRODUCT((D4:D9="*feb*")*(E4:E15="done"))

the result is : 0 ( i assumed that the sumproduct can not identify *
(asterisk) as i tought -- * = any charachter)

the correct one should be : 3

Please help me to solve this problem. I have number of column and hundreds
row needed to be sum with multiple condition of formula such above formula
(summing wihtin 2 condition)

Thank you
Firman
Ericsson Indonesia

help - sumproduct within text range-
 

=SUMPRODUCT((MID(F13:F15,2,3)="feb")*1)+sumproduct ((f13:f15="feb")*1)

this will count cells in your range that contain either feb of have feb
in positions 2:4 within the cell, you can adjust according to what is in
your own cells.


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=522972