Sum product if value present

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a worksheet that totals dollar amounts from another worksheet based on
certain criteria. The current formula is: =SUMPRODUCT(--('JULY
2005'!$D$5:$D$533="KB"),--ISNUMBER('JULY 2005'!$L$5:$L$533),('JULY
2005'!$B$5:$B$533))

I have now want to be able to in certain instances be able to enter another
value in column, and if I do I want it to use the column C value not the
column B, and if there is no value in C use the coulumn B value.

I know I should be able to figure this out, but for some reason I am drawing
a complete blank. Thanks in advance for any help!!

Regards,
 
Is this what you want?

=SUMPRODUCT(--('JULY 2005'!$D$5:$D$53="KB"),--ISNUMBER('JULY
2005'!$L$5:$L$53),(IF(ISNUMBER('JULY 2005'!C5:C53),'JULY 2005'!C5:C53,'JULY
2005'!$B$5:$B$53)))

This is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
You must log in or register to reply here.

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

Scrabble Value calculation for Welsh words 0
Autofill from months 0
countif, sumif, sumproduct - I dont know which to use 3
Search multiple worksheets and return value based on phone number 1
Determine Value based on criteria 1
With F1 value, look at A, if match or partial match return B 5
Return value if two data points match 3
SUMIF result including values not specified in sum-range 2

Back
Top