(I hope I explain this ok without confusing everyone)

I have entered the formula below to do a sort of vlookup and sum calculation
from another worksheet. I've used 'iserror' to show blank where no values
appear, i.e. for rows where the vlookup criteria does not appear, I want the
cells with this formula to show as a blank.

However, the value of the cells used as part of the 'sum' might be blank,
and because of this, there is no result because of the iserror, but I still
want the cells to be added and the result displayed despite there being blank
cells within the calculation.

Have I overcomplicated what I am trying to achieve or am I just missing
something?

=IF(ISERROR(SUMPRODUCT(('PBH Update Sheet'!$A$4:$A$499=$A11)*SUM('PBH Update
Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update
Sheet'!CY4+'PBH Update Sheet'!DM4))),"",IF(SUMPRODUCT(('PBH Update
Sheet'!$A$4:$A$499=$A11)*SUM('PBH Update Sheet'!S4+'PBH Update
Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update Sheet'!BI4+'PBH Update
Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update Sheet'!CY4+'PBH Update
Sheet'!DM4))=0,"",SUMPRODUCT(('PBH Update Sheet'!$A$4:$A$499=$A11)*SUM('PBH
Update Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update
Sheet'!CY4+'PBH Update Sheet'!DM4))))

Hi Sarah

I'm not sure what you are trying to achieve here.
Simplifying your formula it is really
=SUMPRODUCT(($A$4:$A$499=$A11)*
SUM(S4+AG4+AU4+BI4+BW4+CK4+CY4+DM4))

Which would be the number of times cells in the range A4:A499 equal the
value in cell A11, multiplied by the SUM of values found in the relevant
sheet in cells S4+AG4+AU4+BI4+BW4+CK4+CY4+DM4.
So I don't see where the Vllokup and cells where the formula shows a
blank comes into it.

You could get the same result by using
=COUNTIF($A$4:$A$499,$A11)*SUM(S4+AG4+AU4+BI4+BW4+ CK4+CY4+DM4))

I am probably not understanding at all what you are trying to achieve.

--
Regards

Roger Govier


"Sarah (OGI)" wrote in message
...
(I hope I explain this ok without confusing everyone)

I have entered the formula below to do a sort of vlookup and sum
calculation
from another worksheet. I've used 'iserror' to show blank where no
values
appear, i.e. for rows where the vlookup criteria does not appear, I
want the
cells with this formula to show as a blank.

However, the value of the cells used as part of the 'sum' might be
blank,
and because of this, there is no result because of the iserror, but I
still
want the cells to be added and the result displayed despite there
being blank
cells within the calculation.

Have I overcomplicated what I am trying to achieve or am I just
missing
something?

=IF(ISERROR(SUMPRODUCT(('PBH Update Sheet'!$A$4:$A$499=$A11)*SUM('PBH
Update
Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update
Sheet'!CY4+'PBH Update Sheet'!DM4))),"",IF(SUMPRODUCT(('PBH Update
Sheet'!$A$4:$A$499=$A11)*SUM('PBH Update Sheet'!S4+'PBH Update
Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update Sheet'!BI4+'PBH Update
Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update Sheet'!CY4+'PBH Update
Sheet'!DM4))=0,"",SUMPRODUCT(('PBH Update
Sheet'!$A$4:$A$499=$A11)*SUM('PBH
Update Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH
Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update
Sheet'!CY4+'PBH Update Sheet'!DM4))))

I think you have overcomplicated this as SUMPRODUCT will return 0 if no match
found and SUM will handle blank cells so you should be able to remove the
ISERROR test.

Here is another version of your formula (if I have understood it correctly)
which should be entered with Ctrl+Shift+Enter:

=IF((SUMPRODUCT(--('PBH Update
Sheet'!$A$4:$A$499=$A11))*SUM(IF(MOD(COLUMN('PBH Update
Sheet'!S$4:DM$4)-5,14)=0,'PBH Update
Sheet'!S$4:DM$4,0)))=0,"",SUM(IF(MOD(COLUMN('PBH Update
Sheet'!S$4:DM$4)-5,14)=0,'PBH Update Sheet'!S$4:DM$4,0)))

HTH

"Sarah (OGI)" wrote:

(I hope I explain this ok without confusing everyone)

I have entered the formula below to do a sort of vlookup and sum calculation
from another worksheet. I've used 'iserror' to show blank where no values
appear, i.e. for rows where the vlookup criteria does not appear, I want the
cells with this formula to show as a blank.

However, the value of the cells used as part of the 'sum' might be blank,
and because of this, there is no result because of the iserror, but I still
want the cells to be added and the result displayed despite there being blank
cells within the calculation.

Have I overcomplicated what I am trying to achieve or am I just missing
something?

=IF(ISERROR(SUMPRODUCT(('PBH Update Sheet'!$A$4:$A$499=$A11)*SUM('PBH Update
Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update
Sheet'!CY4+'PBH Update Sheet'!DM4))),"",IF(SUMPRODUCT(('PBH Update
Sheet'!$A$4:$A$499=$A11)*SUM('PBH Update Sheet'!S4+'PBH Update
Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update Sheet'!BI4+'PBH Update
Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update Sheet'!CY4+'PBH Update
Sheet'!DM4))=0,"",SUMPRODUCT(('PBH Update Sheet'!$A$4:$A$499=$A11)*SUM('PBH
Update Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update
Sheet'!CY4+'PBH Update Sheet'!DM4))))

Thank you - I am going to try this now.
What does the -- mean in the formula? and what do you mean by 'it should be
entered with Ctrl+Shift+Enter?

(sorry if I'm asking daft questions!)

"Toppers" wrote:

I think you have overcomplicated this as SUMPRODUCT will return 0 if no match
found and SUM will handle blank cells so you should be able to remove the
ISERROR test.

Here is another version of your formula (if I have understood it correctly)
which should be entered with Ctrl+Shift+Enter:

=IF((SUMPRODUCT(--('PBH Update
Sheet'!$A$4:$A$499=$A11))*SUM(IF(MOD(COLUMN('PBH Update
Sheet'!S$4:DM$4)-5,14)=0,'PBH Update
Sheet'!S$4:DM$4,0)))=0,"",SUM(IF(MOD(COLUMN('PBH Update
Sheet'!S$4:DM$4)-5,14)=0,'PBH Update Sheet'!S$4:DM$4,0)))

HTH

"Sarah (OGI)" wrote:

(I hope I explain this ok without confusing everyone)

I have entered the formula below to do a sort of vlookup and sum calculation
from another worksheet. I've used 'iserror' to show blank where no values
appear, i.e. for rows where the vlookup criteria does not appear, I want the
cells with this formula to show as a blank.

However, the value of the cells used as part of the 'sum' might be blank,
and because of this, there is no result because of the iserror, but I still
want the cells to be added and the result displayed despite there being blank
cells within the calculation.

Have I overcomplicated what I am trying to achieve or am I just missing
something?

=IF(ISERROR(SUMPRODUCT(('PBH Update Sheet'!$A$4:$A$499=$A11)*SUM('PBH Update
Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update
Sheet'!CY4+'PBH Update Sheet'!DM4))),"",IF(SUMPRODUCT(('PBH Update
Sheet'!$A$4:$A$499=$A11)*SUM('PBH Update Sheet'!S4+'PBH Update
Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update Sheet'!BI4+'PBH Update
Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update Sheet'!CY4+'PBH Update
Sheet'!DM4))=0,"",SUMPRODUCT(('PBH Update Sheet'!$A$4:$A$499=$A11)*SUM('PBH
Update Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update
Sheet'!CY4+'PBH Update Sheet'!DM4))))

When you put the formula in the cell, hold down Ctrl and Shift keys and hit
Enter.

BUT look at Roger's solution ... it is simpler if it meets your need.

"Sarah (OGI)" wrote:

Thank you - I am going to try this now.
What does the -- mean in the formula? and what do you mean by 'it should be
entered with Ctrl+Shift+Enter?

(sorry if I'm asking daft questions!)

"Toppers" wrote:

I think you have overcomplicated this as SUMPRODUCT will return 0 if no match
found and SUM will handle blank cells so you should be able to remove the
ISERROR test.

Here is another version of your formula (if I have understood it correctly)
which should be entered with Ctrl+Shift+Enter:

=IF((SUMPRODUCT(--('PBH Update
Sheet'!$A$4:$A$499=$A11))*SUM(IF(MOD(COLUMN('PBH Update
Sheet'!S$4:DM$4)-5,14)=0,'PBH Update
Sheet'!S$4:DM$4,0)))=0,"",SUM(IF(MOD(COLUMN('PBH Update
Sheet'!S$4:DM$4)-5,14)=0,'PBH Update Sheet'!S$4:DM$4,0)))

HTH

"Sarah (OGI)" wrote:

(I hope I explain this ok without confusing everyone)

I have entered the formula below to do a sort of vlookup and sum calculation
from another worksheet. I've used 'iserror' to show blank where no values
appear, i.e. for rows where the vlookup criteria does not appear, I want the
cells with this formula to show as a blank.

However, the value of the cells used as part of the 'sum' might be blank,
and because of this, there is no result because of the iserror, but I still
want the cells to be added and the result displayed despite there being blank
cells within the calculation.

Have I overcomplicated what I am trying to achieve or am I just missing
something?

=IF(ISERROR(SUMPRODUCT(('PBH Update Sheet'!$A$4:$A$499=$A11)*SUM('PBH Update
Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update
Sheet'!CY4+'PBH Update Sheet'!DM4))),"",IF(SUMPRODUCT(('PBH Update
Sheet'!$A$4:$A$499=$A11)*SUM('PBH Update Sheet'!S4+'PBH Update
Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update Sheet'!BI4+'PBH Update
Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update Sheet'!CY4+'PBH Update
Sheet'!DM4))=0,"",SUMPRODUCT(('PBH Update Sheet'!$A$4:$A$499=$A11)*SUM('PBH
Update Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update
Sheet'!CY4+'PBH Update Sheet'!DM4))))

I have two worksheets, both with column A showing client ref. In the Summary
sheet I need to do a vlookup against the client ref to find and sum details
in the PBH Udate Sheet. The latter contains data that is split into blocks,
according to company, then by 14 columns per block.

In one column in the summary sheet, I need to vlookup the client ref, so for
each ref I can find the total amount of business placed - this is done by
summing the relevant cell in the first column of each block, then in another
column summing the relevant cell in the second column of each block, and so
on. Some of the cells might be blank but I will need a calculation that
shows a value, but where a vlookup criteria is not available, I want the cell
to be blank.

Example:

A B C D E F G
1 Company A Company B
2 Client Ref Set Up Value Visit 1 Value Visit 2 Value Set Up Value
Visit 1 Value Visit 2 Value
3 AAA 10 11 2 4 6
4 BBB 5 7 8 3 6
5 CCC 8 5 8 10 10 11
6 DDD 12 15 10 5 10 15


Summary

A B C D
1 Client Ref Set Up Value Visit 1 Value Visit 1 Value
2 AAA 12 (B3+E3) 15 (C3+F3) 6 (D3+G3)
3 BBB 8 (B4+E4) 13 (C4+F4) 8 (D4+G4)
4 CCC 18 (B5+E5) 15 (C5+F5) 19 (D5+G5)
5 DDD 17 (B6+E6) 25 (C6+F6) 25 (D6+G6)


"Roger Govier" wrote:

Hi Sarah

I'm not sure what you are trying to achieve here.
Simplifying your formula it is really
=SUMPRODUCT(($A$4:$A$499=$A11)*
SUM(S4+AG4+AU4+BI4+BW4+CK4+CY4+DM4))

Which would be the number of times cells in the range A4:A499 equal the
value in cell A11, multiplied by the SUM of values found in the relevant
sheet in cells S4+AG4+AU4+BI4+BW4+CK4+CY4+DM4.
So I don't see where the Vllokup and cells where the formula shows a
blank comes into it.

You could get the same result by using
=COUNTIF($A$4:$A$499,$A11)*SUM(S4+AG4+AU4+BI4+BW4+ CK4+CY4+DM4))

I am probably not understanding at all what you are trying to achieve.

--
Regards

Roger Govier


"Sarah (OGI)" wrote in message
...
(I hope I explain this ok without confusing everyone)

I have entered the formula below to do a sort of vlookup and sum
calculation
from another worksheet. I've used 'iserror' to show blank where no
values
appear, i.e. for rows where the vlookup criteria does not appear, I
want the
cells with this formula to show as a blank.

However, the value of the cells used as part of the 'sum' might be
blank,
and because of this, there is no result because of the iserror, but I
still
want the cells to be added and the result displayed despite there
being blank
cells within the calculation.

Have I overcomplicated what I am trying to achieve or am I just
missing
something?

=IF(ISERROR(SUMPRODUCT(('PBH Update Sheet'!$A$4:$A$499=$A11)*SUM('PBH
Update
Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update
Sheet'!CY4+'PBH Update Sheet'!DM4))),"",IF(SUMPRODUCT(('PBH Update
Sheet'!$A$4:$A$499=$A11)*SUM('PBH Update Sheet'!S4+'PBH Update
Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update Sheet'!BI4+'PBH Update
Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update Sheet'!CY4+'PBH Update
Sheet'!DM4))=0,"",SUMPRODUCT(('PBH Update
Sheet'!$A$4:$A$499=$A11)*SUM('PBH
Update Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH
Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update
Sheet'!CY4+'PBH Update Sheet'!DM4))))

Hi Sarah

In my opinion, this type of data layout is fraught with difficulties.
If you could recast your data as follows

Company Client Ref Visit Type Visit Value
CompanyA AAA Setup 10
CompanyA AAA First 2
CompanyA AAA Second 6
CompanyB EEE Setup 25
CompanyC HHH First 30
etc.

The order is totally unimportant, and you might need 2 value columns (I
can't quite tell from your data) associated with each visit.

With data in this format, it would be simple to analyse with a Pivot
Table.
Mark the block of dataDataPivot TableNextNextFinish
Drag Company to the Page Area
Drag Client to the Row Area
Drag Visit Type to the Column Area
Drag Visit Value ( plus your second Visit value2 column if you have one)
to the Data Area. If it shows as Count of, then double click filed
heading and choose Sum)
If you do have two value columns (they must have different titles) then
drag the Data filed and drop in on Total to show the values side by
side.

For more help on Pivot Tables, take a look at Debra Dalgleish's site
http://www.contextures.com/xlPivot02.html

Look at Debra's methods of defining Dynamic Ranges for your data so that
it grows automatically as you add more data.

This might seem a lot of work to change your data layout, but in the
long run I think it will be beneficial and easier to maintain.

--
Regards

Roger Govier


"Sarah (OGI)" wrote in message
...
I have two worksheets, both with column A showing client ref. In the
Summary
sheet I need to do a vlookup against the client ref to find and sum
details
in the PBH Udate Sheet. The latter contains data that is split into
blocks,
according to company, then by 14 columns per block.

In one column in the summary sheet, I need to vlookup the client ref,
so for
each ref I can find the total amount of business placed - this is done
by
summing the relevant cell in the first column of each block, then in
another
column summing the relevant cell in the second column of each block,
and so
on. Some of the cells might be blank but I will need a calculation
that
shows a value, but where a vlookup criteria is not available, I want
the cell
to be blank.

Example:

A B C D E F G
1 Company A Company B
2 Client Ref Set Up Value Visit 1 Value Visit 2 Value Set Up Value
Visit 1 Value Visit 2 Value
3 AAA 10 11 2 4 6
4 BBB 5 7 8 3 6
5 CCC 8 5 8 10 10 11
6 DDD 12 15 10 5 10 15


Summary

A B C D
1 Client Ref Set Up Value Visit 1 Value Visit 1 Value
2 AAA 12 (B3+E3) 15 (C3+F3) 6 (D3+G3)
3 BBB 8 (B4+E4) 13 (C4+F4) 8 (D4+G4)
4 CCC 18 (B5+E5) 15 (C5+F5) 19 (D5+G5)
5 DDD 17 (B6+E6) 25 (C6+F6) 25 (D6+G6)


"Roger Govier" wrote:

Hi Sarah

I'm not sure what you are trying to achieve here.
Simplifying your formula it is really
=SUMPRODUCT(($A$4:$A$499=$A11)*
SUM(S4+AG4+AU4+BI4+BW4+CK4+CY4+DM4))

Which would be the number of times cells in the range A4:A499 equal
the
value in cell A11, multiplied by the SUM of values found in the
relevant
sheet in cells S4+AG4+AU4+BI4+BW4+CK4+CY4+DM4.
So I don't see where the Vllokup and cells where the formula shows a
blank comes into it.

You could get the same result by using
=COUNTIF($A$4:$A$499,$A11)*SUM(S4+AG4+AU4+BI4+BW4+ CK4+CY4+DM4))

I am probably not understanding at all what you are trying to
achieve.

--
Regards

Roger Govier


"Sarah (OGI)" wrote in message
...
(I hope I explain this ok without confusing everyone)

I have entered the formula below to do a sort of vlookup and sum
calculation
from another worksheet. I've used 'iserror' to show blank where no
values
appear, i.e. for rows where the vlookup criteria does not appear, I
want the
cells with this formula to show as a blank.

However, the value of the cells used as part of the 'sum' might be
blank,
and because of this, there is no result because of the iserror, but
I
still
want the cells to be added and the result displayed despite there
being blank
cells within the calculation.

Have I overcomplicated what I am trying to achieve or am I just
missing
something?

=IF(ISERROR(SUMPRODUCT(('PBH Update
Sheet'!$A$4:$A$499=$A11)*SUM('PBH
Update
Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH
Update
Sheet'!CY4+'PBH Update Sheet'!DM4))),"",IF(SUMPRODUCT(('PBH Update
Sheet'!$A$4:$A$499=$A11)*SUM('PBH Update Sheet'!S4+'PBH Update
Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH Update Sheet'!BI4+'PBH
Update
Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH Update Sheet'!CY4+'PBH
Update
Sheet'!DM4))=0,"",SUMPRODUCT(('PBH Update
Sheet'!$A$4:$A$499=$A11)*SUM('PBH
Update Sheet'!S4+'PBH Update Sheet'!AG4+'PBH Update Sheet'!AU4+'PBH
Update
Sheet'!BI4+'PBH Update Sheet'!BW4+'PBH Update Sheet'!CK4+'PBH
Update
Sheet'!CY4+'PBH Update Sheet'!DM4))))