Excel array formula help please?

Question

"I'm missing a piece of the puzzle here and I can't get the formula to work.

I have sales opportunity data and I'm trying to get it to add up the dollar amounts if column A equals "BB sales cycle" and Column B equals "A". It's not working and I'm getting a NUM error. Here's a sample of the data...

COLUMN A COLUMN B COLUMN C
BB Sales Cycle A $105,000
BB Sales Cycle B $100,000
MM Sales Cycle B $98,000
BB Sales Cycle A $100,000

The result should be $205,000. Can anyone help with the formula? Sorry I can't get the question to show in columns of any kind - the data is smooshed.

I'm looking for a COUNTIF, SUMIF, etc. formula. I have about 8,000 rows of data. It would be similar to the following formula, except this would only count:

=SUM(IF((A:A="BB Sales Cycle)*(B:B="A"),1))

That formula will count the number of occurences of that array of data. The result for the data sample with this formula would be 2. But I want to count the dollars in the third column.

The formula ended up being as follows:
=SUM((A1:A4="BB Cycle")*(B1:B4="A")*(C1:C4)
CRTL+SHIFT+ENTER

Answer 1

=SUMPRODUCT((A1:A4="BB Sales Cycle")*(B1:B4="A")*(C1:C4))

Answer 2

You can put your BB sales and your MM sales in 2 seperate columns....or you can do something like this:
=(c1+c2+c3+c4-c3)
....If I'm understanding correctly...you could try to click on the help menu un excel under formulas.......sorry I can't be more help...not sure I completely understand your question.

Answer 3

I don't think that you can directly do that..
Add a new column (D for example) that equals to =A&B,
so that you can later on do something like
=SUMIF(D2:D5,"BB Sales Cycle"&"B",C2:C5)

You can hide that column D after if you don't want around.

Hope that helps.