If column A is a June date, return the dollar value for that line item.

dearickmilton · #1 06-08-2009, 09:31 PM

Hello. I'm pretty new at using formulas in Excel.

Worksheet A: Data
1) I have a list of customers alphabetically lined up for training, Column A, rows 1-365.
2) In Column B, I have the date they're scheduled to be trained.
3) In Column C, I have the forecasted revenue (i.e., $275.00.)

Worksheet B: Summary
1) Row A1 = total number of customers to train.
2) Row A2 = total number of customers to train in June 2009.
3) Row A3 = total number of customers to train in July 2009.
3) Row A4 = forecasted revenue from customers trained in June 2009.
4) Row A5 = forecasted revenue from customers trained in July 2009.

I think I have the formula for Row A1; however, instead of sorting Column B by date, I'd like to keep Column A in alphabetical order.

How do I write a formula so that Excel looks at Column B on the "Data" worksheet and, if a June date, add the corresponding forecasted revenue to Row A4 on the "Summary" worksheet?

Thank you very much.

NBVC · #2 06-09-2009, 09:39 AM

Try:

=Sumproduct(--(Text(Data!$B$2:$B$100,"MMMYY")="Jun09"),Data!$C$2:$C$100)

adjust ranges to suit.

Welcome to the Excel Forum

Forum Statistics