Biff, i re-do the format in a new worksheet n the formula is working

i need your advice on below
Is there a formula to calculate below?

For example, we are currently in the month of May,
Actual consultants utilized (past months) vs No.
of consultants utilized (Plan) -- forward month
Region A Jan - April data
May - Dec data
Region B Jan - April data
May - Dec data
Region C Jan - April data
May - Dec data
When we reach the month of June, the formula will be to detect the date and
make a auto-calculation?

Data Details
Region Resource Utilization Month No of Consultants Utilized (Plan) Plan
Days Plan Util (%) No of Consultants Utilized (Actual) Actual Days Util (%)
CN BPM Jan. 2008 18 22 0.438016529 0 0 0
Cn BPM Feb. 2008 13 17 0.444444444 13 18 0.424242424
Cn BPM Mar. 2008 22 30 0.735930736 21 28.5 0.602813853
Cn BPm Apr. 2008 20 16 0.361471861 13 18.5 0.364718615
CZ BPm May. 2008 17 27 0.620454545 0 0 0
CZ BPm June. 2008 18 32 0.807017544 0 0 0
PH BPm July. 2008 19 36 0.814645309 0 0 0
PH BPm Aug. 2008 18 2 0.807017544 0 0 0
PH BPm Sept. 2008 20 71 0.843181818 0 0 0
PH BPm Oct. 2008 20 9 0.8475 0 0 0
MY BPm Nov. 2008 20 37 0.8175 0 0 0
MY BPm Dec. 2008 20 35 0.815217391 0 0 0

thanks...
judy goh


"T. Valko" wrote:

Can you post a small sample of your data so I can what you're trying to do?

Geezzzz! I left out the most important word.

Can you post a small sample of your data so I can see what you're trying to
do?

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Can you post a small sample of your data so I can what you're trying to
do?

--
Biff
Microsoft Excel MVP


"judy" wrote in message
...
Hi Biff
I'm encountering another issue.
the formula is working for the Product A.
when i applied the formula to Product B, the values show as 0 (though the
details have a value tied to it)

I tried to re-calculate the formula by doing a Ctrl-ALT-F9 but it's still
showing as 0 values. Any idea why this is happening?

Product A, Product B
Local SG 25, 0
Local Others 7, 0
Sub 8,0
--
judy goh


"T. Valko" wrote:

For "Local SG" :

=SUMPRODUCT(--(ISNUMBER(SEARCH("Local
SG",$E$24:$E$43))),--($G$24:$G$43="Actual"),$H$24:$H$43)

For all others:

=SUMPRODUCT(--(ISERROR(SEARCH("Local
SG",$E$24:$E$43))),--($G$24:$G$43="Actual"),$H$24:$H$43)



--
Biff
Microsoft Excel MVP


"judy" wrote in message
...
Hi Biff

Is there a way this formula can further cater to below?

For example
I have local Sg, local CN, local CZ, local MY fields and i'm trying to
achieve below:

SUM all for local SG compared to SUM all for the rest of the regions,
excluding SG.

=SUMPRODUCT(--(ISNUMBER(SEARCH("Local",$E$24:$E$43))),--($G$24:$G$43="Actual"),$H$24:$H$43)
--
judy goh


"T. Valko" wrote:

Is the wildcard feature not available in SUM+IF

No, it can't be used directly in those functions.

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Local",$E$24:$E$43))),--($G$24:$G$43="Actual"),$H$24:$H$43)


--
Biff
Microsoft Excel MVP


"judy" wrote in message
...
Hello!
I was able to use wildcards in SUMIF formulas but it just don't
work in
SUM+IF statement. Is the wildcard feature not available in SUM+IF
statement
or there's something wrong with my formula?

=SUMIF($E$2:$E$300, "Local*", $F$2:$F$300)
This will help me sum up the F column if column E contains Local 1,
Local
2,
Local 3 etc

The formula I'm trying to create now is as follow
=SUM(IF(($E$24:$E$43="Local*")*($G$24:$G$43="Actua l"),$H$24:$H$43))
However, the formula is not looking at Local 1, Local 2, Local 3 of
column
E. It doesnt seem to recognise this feature.

Can any of you help advise me on above? Thanks soo much!!!