Adding Range Names

  • Thread starter Thread starter John
  • Start date Start date
J

John

I am trying to add the Range Name - VATHOBO to NetSalesHOBO to the existing
formula below, but can't get the syntax correct. Perhaps someone could help


=IF(ISERROR(SUMPRODUCT(--(SalesDate=AH27),NetSalesHOBO))/100,0,SUMPRODUCT(--
(SalesDate=AH27),NetSalesHOBO))/100
 
Did it this way, not sure if its the most efficient

=IF(ISERROR(SUMPRODUCT(--(SalesDate=AH9),NetSalesHOBO))/100,0,SUMPRODUCT(--(
SalesDate=AH9),NetSalesHOBO))/100+IF(ISERROR(SUMPRODUCT(--(SalesDate=AH9),VA
THOBO))/100,0,SUMPRODUCT(--(SalesDate=AH9),VATHOBO))/100
 
John,

This is a bit simpler

=IF(ISERROR(SUMPRODUCT(--(SalesDate=AH9),(NetSalesHOBO+VATHOBO))),0,SUMPRODU
CT(--(SalesDate=AH9),(NetSalesHOBO+VATHOBO)))/100
 
I don't understand why you would want to invoke a SumProduct formula
with just one condition. Your current formula can be replaced just with:

=SUMIF(SalesDate,AH27,NetSalesHOBO)/100

To also consider a second range to sum with the same condition:

=(SUMIF(SalesDate,AH27,NetSalesHOBO)+SUMIF(SalesDate,AH27,VATHOBO))/100
 
Thanks Bob

Bob Phillips said:
John,

This is a bit simpler

=IF(ISERROR(SUMPRODUCT(--(SalesDate=AH9),(NetSalesHOBO+VATHOBO))),0,SUMPRODU
CT(--(SalesDate=AH9),(NetSalesHOBO+VATHOBO)))/100

--
HTH

Bob Phillips


=IF(ISERROR(SUMPRODUCT(--(SalesDate=AH9),NetSalesHOBO))/100,0,SUMPRODUCT(--(
SalesDate=AH9),NetSalesHOBO))/100+IF(ISERROR(SUMPRODUCT(--(SalesDate=AH9),VA
=IF(ISERROR(SUMPRODUCT(--(SalesDate=AH27),NetSalesHOBO))/100,0,SUMPRODUCT(--
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMPRODUCT has mismatched range sizes error when sheet opened inGoogle Excel. 1
Help with TEXT function 3
Sum daily sales by week 3
Return cell content in a matched range 2
Looking up a MAX Date within a Range Q 4
Find MIN of Dates and Returns Value 5
Sumproduct across mutliple columns 3
Sumproduct range changing with deletions 6

Back
Top