need to count two true conditions

  • Thread starter Thread starter mjluck
  • Start date Start date
M

mjluck

I new at this and am trying to update an old formula in a pay sheet to
new conditions.
It is to do with broken meals; in the old formula it only gives one per
day. In the real world now we could have four or five broken meals a
day. I need a formula that will count the number of times.
The present formula is
=IF(ISERROR(VLOOKUP(D25,'OVERTIME AND
MEALS'!$B$33:$Q$50,8,FALSE)),"",IF(VLOOKUP(D25,'OVERTIME AND
MEALS'!$B$33:$Q$50,8)>0,1,""))
As far as I understand it the date is in D25 of the timesheet checks
with the data in range B33:Q50 of the overtime and meals sheet and if a
time is entered in the column eight of the same row as the date, a 1 is
shown in the formula column. But if the same combination happens again
there is no increase
 
Would the following do what you are after:-

=SUMPRODUCT(('OVERTIME AND MEALS'!$B$33:$B$50=$D$25)*('OVERTIME AND MEALS'!$I$33:$I$50>0))

Is a zero an acceptable answer if there are none, or do you need to hide those. If so then you
can use Conditional formatting to turn the font white if the cell value = 0, or you can build it
into the formula, but it does start getting bigger:-

=IF((SUMPRODUCT(('OVERTIME AND MEALS'!$B$33:$B$50=$D$25)*('OVERTIME AND
MEALS'!$I$33:$I$50>0)))=0,"",SUMPRODUCT(('OVERTIME AND MEALS'!$B$33:$B$50=$D$25)*('OVERTIME AND
MEALS'!$I$33:$I$50>0)))
 
many thanks that worked first time and will now make this speadsheet
work for all the non computer users

malcolm
 
Back
Top