Hi all



I'm a novice at formula and have searched the internet to find out the answer to my question. I have cobbled together a formula, which works but I feel is too long and there must be an easier way of doing it.

I read about arrays but do not understand them and I am not sure if it's the right thing to use for this calculation.

In brief, I want to be able to add the cells together in rows that contain E (which will equal 7.5) L (which will equal 8) and variations of numbers i.e. 12-8 (which will equal 8).

This is an example of what I have put together from copying the first bit from another post. I do not understand what the first bit means, but it does work:

=IF(COUNTIF(A13:G13,"="""),"",SUM(COUNTIF(A13:G13, "=L")*8,COUNTIF(A13:G13,"=E")*7.5,COUNTIF(A13:G13, "=9-4")*7,COUNTIF(A13:G13,"=12-8")*8,COUNTIF(A13:G13,"=AD")*7,COUNTIF(A13:G13,"=A L")*7.4))

Thank you for taking the time to read my post, I hope you can help as it will save so much time checking hours for a rota.

How about:

=SUM(COUNTIF(A13:G13,{"L","E","9-4","12-8","AD","A L"})*{8;7,5;7;8;7;7,4})

(I assume you really mean there should be a space between A and L).

xor, thank you for your reply. I have tried the formula that you've suggested but I can't get it to work.

The A and L should be together as this is the code used for Annual Leave and it should equal 7.4. I sort of understand how the formula works but can you please explain why there are semi colons in the last bit instead of commas?

Thanks again for your reply, I will look at it again tomorrow as it's almost midnight and I have an early start :-(

Excuse me that I forgot to change some semi colons to commas.

Attached Files

SumCountif.xlsx (15.8 KB, 22 views)

Hi, it worked once I changed the semi colons to commas. Thank you so much it is really appreciated. As I am new on here I don't know how to put the "solved" icon on my post do you know how?

I don't think it is important with this "solved" icon (and I don't know how).

Did you remember to remove that space between A and L (if you do not use the file I uploaded)?

I did, and thank you once again