Enter 2 formulas in 1 cell

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

Guest

I need to enter 2 formuals in one cell and I do not know what the formula
should be. Example....I need to count the number of cells that contain the
word "internet" but only if in another cell it has an amount in it. Any
ideas? Thanks!
 
Hi,

You aren't very specific when you say another cell has an amount in so this
assumes an amount is anything > 0 but that condition could equaly be

<>""
<0
Or many others.

=SUMPRODUCT((A1:A20="Internet")*(B1:B20>0))

Mike
 
One way is to try sumproduct, something like this in say, C1:
=sumproduct((a1:a10="internet")*(b1:b10<>""))
Adapt the ranges to suit

a1:a10 is where the text "internet" would appear, while
b1:b10 would contain the amounts, if any
 
Can you please help me with my next step...in addition to my previous email,
now in another cell I need to add up the totals of the amounts in the cells
only that only contained the word "internet" ..Thanks!
 
Needs help said:
.. need to add up the totals of the amounts in the cells
only that only contained the word "internet" ..Thanks!
Click to expand...

Assuming B1:B20 contain the amounts to be summed,
with A1:A20 housing text such as "internet"

If you're still talking multi-conditions, then use eg:
=SUMPRODUCT((A1:A20="Internet")*(B1:B20>=100),B1:B20)
which will sum only the amounts >=100 in col B
where col A contains: internet

If it's only for a single condition, use SUMIF, eg:
=SUMIF(A1:A20,"internet",B1:B20)

Think SUMIF will suffice for you here

---
 
Back
Top