Sumproduct and dividing with blank rows

[Flashart]

Hi

I am hoping someone could help me.

I am trying to divide a range that has blanks in. The range is the result of a webquery and taking the blanks out would be time consuming at best. Conveniently (potentially) the blank row is every other row in the range.

Please Login or Register  to view this content.

is what I have so far. T4 represents a 3 letter code that is listed in various places within the range A5-A100. E5:E500 is a range called Contribution. Range B5:B100 is a range called bookings. This code though gives me DIV/0 as would be expected due to blanks in the range.

What I am trying to achieve is a Contribution per Bkg by dividing the Contribution by the bookings.

If anyone could help I would be most grateful.

Kind Regards
Peter

[daddylonglegs]

Hello Peter, you can "filter out" the blanks like this

=SUM(IF(ISNUMBER(SEARCH(T4,$A$5:$A$100)),IF(ISNUMBER($B$5:$B$100),$E$5:$E$100/$B$5:$B$100)))

This is an "array formula" which needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar

[Flashart]

Hi

Thanks for the reply. I think it might be easier if I show you the spreadsheet itself!

I have attached the s/sheet and moved the columns together to make it easier.

In the table is the codes with a blank column for the Contribution per bkg.

Taking the first one ALT, I need the formula to look down Column A for any occurence of ALT, then look at the relevant bookings and contribution then divide the contribution by the bookings to give me the Contribution Per Booking.

The formula you gave me earlier was close, and I appreciate I may have moved the goalposts somewhat for which I apologise!

Essentially though what should happen is;

All the ALT's bookings be added (132)
All the Contribution for each occurence of ALT added together (£1828.53)
Then divide the Contribution by bookings (£1828.53 / 132)
Result = Contribution per Bkg of £13.85

The Contribution per Bkg is the only figure that needs to be displayed in the cell H5. And so on down the table.

I have amended your formula to take into account the change in column structure. You can see how it gives a result of £35.09.

Also the 100 rows within the formula is so that if the web query is refreshed and new codes appear, there is enough leeway for this to occur.

Thanks for any help you can give and I do appreciate what you have done so far.

Kind regards
Peter

[DonkeyOte]

Why not use non-arrays, ie 2 SUMIFs ?

H4: =SUMIF($A$5:$A$100,"*-"&$G4,$C$5:$C$100)/SUMIF($A$5:$A$100,"*-"&$G4,$B$5:$B$100)

[Flashart]

Thanks for the reply.

Certainly does the job! I hadn't thought of doing that. Wood and trees springs to mind!
Thanks ever so much!

Kind regards
Peter