Re: Excel Sumproduct

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

i don't think it would be possible because i have a large list of names, and
i think 20 reason to be on holiday.

could you help me translate this to vba so that i can use this on my formula

my simple equation is :

=SUMPRODUCT(($G11=sn)*(sw="CRA")*(sf<=H$7)*(st>=H$7))

$G11 and H$7 are variables
SN,SW,SF,ST are ranges

a vba function will be great.

thanks
"Bob Phillips" wrote:



"mikel" <mikel@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B38DFF16-3D78-4155-97D3-7A8DFDE55AD7@xxxxxxxxxxxxxxxx
Hi Bob,

Thanks for the interest in helping,

most probably, the logic behind the simple holiday planner is known right?

Not here!


wherein we have column a, b, c as source for the sumproduct function.
a range = SNAMES, b range = SFROM, c range = STO.

but my problem is i want to have another column D for whereabouts like
what
is the reason for the holiday, is it Vacation leave, illness etc.

so i want to plot the dates based on NAME and whereabout...

the output should somehow be like this but i want it to automatically plot
the dates in the calendar
INPUT DATA:
A B C D
Snames Sfrom Sto Swhere
Mikel Jan 1 Jan 4 Illness

OUTPUT:

January
1 2 3 4
Mikel ILL ILL ILL ILL
etc.

since sumproduct displays output as number, i have a custom if function
wherein if the output of sumprod is equal to lets say 4 it will display
ILL
etc.,
that is the reason why multiply my formula to different number to have
different output based on whereabouts.

Hope you get mine.

and thanks in advance for your help

If I understand, and that is a big if, you want to extract from a table the
number of days of illness and then plot this in another table?

So what happens they are Ill between 1/4 Jan, and on vacation between 10/15.

The way i do this sort of thing is to have a gantt type table, name in
column A, start date in B, end date in C, reasoon in D. In E on in row 1 I
have dates. Then I check in E2 etc like so

=IF(AND($B2<=E$1,$C2>=E$1),VLOOKUP($D2,{"Illness","I";"Holiday","H"},2,FALSE),"")

and probably use conditional formatting to colour the cells for visual
effect.



.

Relevant Pages

  • Re: Excel Sumproduct
    ... Why do you want VBA, ... is the reason for the holiday, is it Vacation leave, illness etc. ... so i want to plot the dates based on NAME and whereabout... ... Mikel Jan 1 Jan 4 Illness ...
    (microsoft.public.excel.misc)
  • Re: What era are we in?
    ... the Gunpowder Treason and plot ... I see no reason ... that Gunpowder Treason ... It is not a holiday. ...
    (rec.arts.sf.written)
  • Re: What era are we in?
    ... the Gunpowder Treason and plot ... I see no reason ... that Gunpowder Treason ... It is not a holiday. ...
    (rec.arts.sf.written)
  • Re: New Avengers #21: I Hate You Marvel. And Your Little Dog Too.
    ... entertainment is not the *only* rational reason for reading fiction. ... They don't want to hear any criticism from readers. ... have in common is their lack of concern about plot content. ... formula fiction, such as Fluke's, and serial comics. ...
    (rec.arts.comics.marvel.universe)
  • Re: Just a personal dread
    ... we have to somehow scrape enough dough together to find gifts ... needed while the "holiday" season progresses. ... makes us refuse to let the weather "make" us do or wear something we ... scale is the reason for my extended "medical leave". ...
    (alt.support.chronic-pain)