Offsetsumproduct help

[steve89]

hello all i have a formula which is the following

=SUMPRODUCT((OFFSET($A8,0,1,1,372)<>"")*(OFFSET($A$3,0,1,1,372))*((OFFSET($A8,0,1,1,372))=NL$5))

A8 = EMPLOYEE NAME
A3 = the current month
NL = an infraction or paid time off

my question is how can i get this to equal multiple arguments so NL at this point equals vacation day if "V' is entered in the calendar for all months but i want it to calculate if there was "v" and "SV" which is sick with vacation pay just looking at add multiple arguments because this is a macro which uses only a scroll options for the months in a year so a countif formula wont work here.

 

[Jonmo1]

How about

=SUMPRODUCT(SUMIF($B8:$NI8,NL$5:NL$6,$B$3:$NI$3))

NL5 = V
NL6 = SV

 

[steve89]

that wont work because its a calendar so if i change the month that has to match the month and when its entered its simply counting to date and that cant happy only when we change the month like if we change it ti january its should say what type of paid time off was used and then when february comes if nothing was used then it should read zero.

 

[Jonmo1]

It's difficult to visualize your sheet, and how each cell relates to your formula.

Can you show a sample of your table, Try using the HTML Editor to post a nicely formatted table.
See my signature for a link

 

[steve89]

im sorry i have no idea how to use that im trying to now but not sure how....

A5:CP5 = dates in the months
A6:CP6 = Days translated from A5
the arrow on BX3 & CH3 is used to change the months of the year so its all on one sheet.

A1 = STARTING MONTH
A2 = THE YEAR
A3 = the current month but changes as you change the months with the arrows on BX3 & CH3
A8 = EMPLOYEE NAME
NL5 = an infraction or paid time off at your discretion but i want the formula to argue if i enter V or VS to equal and find the same throughout the year

this is the formula i once used but wont work if the month is changing on a sheet =+COUNTIF(B8:AF8,"S")*1+COUNTIF(B8:AF8,"AS")*1
[TABLE="width: 364"]
<tbody>[TR]
[TD][/TD]
[TD="align: left"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

You just highlight your range, click on the HTML Maker tab, select an option and it copies your range as HTML code that you then paste here.

We have a testing forum you can use to try it