VLookup

[Guest]

Hi

I need some help with i think a vlookup formula.

I have a spreadsheet that calculates services done by shops.

there are 8 sheets 1 for each day of the week and 1 summary.

On the daily sheets there are 30 drop down boxes where the individual can
input a sevice in each box.

On the summary sheet there is a table which breaks down the number of
individual services and i want it to automatically pull how many of each
services have been done over the whole week i.e tabs 1-7

Can anyone help?

phill

 

[Guest]

I suspect you'll use countif rather than vlookup.
Ex: =countif('Monday'!A:A,"Repair") will indicate how many cells in column
A on Monday's worksheet are set to 'Repair'. So if you have the word Repair
in cell A2 of the summary sheet, then you could use something like
=countif('Monday'!A:A,$A2)+countif('Tuesday'!A:A,$A2)... to get the full
week's count of Repair services.

 

[Guest]

thankyou

what would i put in the equation so that it does what you said but it
ignores any blank cells from the individual days, as they are on drop down
boxes and may or may not be filled in depending on how busy they are?

Thanks

Phill

 

[Guest]

I'm not sure I understand... If the drop-down selection boxes are blank,
then the countif function wouldn't count them, so you should be fine.
If you have a case where the drop-down selection would have been made, but
another cell in the same row might be blank indicating that there was no
activity, you could use sumproduct to capture both conditions, the activity
being selected AND the other cell non-blank. The formula below would provide
the count if the activity drop-downs are in A1:A40 and the corresponding
cells are in B1:B40.
=SUMPRODUCT(--(Monday!$A$1:$A$40=$A2),--(Monday!$B$1:$B$40<>""))