Formula for multiple text fields

[amyk82]

Hi,

I have been trying to use the SUMIF formula to calculate sums pulling information from 2 separate columns containing text

Column B = Text fields - e.g. Retail
Column H = Total $ amount
Column K = Text - e.g Sold

I'm trying to find a formula that will calculate the total $ for the enquiries listed on the spreadsheet that are of "Retail" type and those enquiries that "Sold".

I have tried using (=SUMIFS($H1:$H33,$B1:$B35,"RETAIL", $K1:$K33, "Sold")

I have tried a few variations but it keeps giving me the #VALUE response or tells me that my formula has too many arguments.

Any assistance would be great

Thanks

[XOR LX]

Hi,

Your ranges need to be of equal size. Your second currently extends to row 35.

Regards

[amyk82]

Thank you so much

I can't believe i missed that - that's what I get for staring at the computer for hours.

Thanks again much appreciated!

[XOR LX]

We all do it!

And you're welcome!

[amyk82]

ACTUALLY - now I am stuck with the second part.

I also have a corresponding column for each enquiry type that shows how many enquiries of that type were sold.

I also need to show another summary showing how many clients were included in that enquiry

IE:

Column B = Text fields - e.g. Retail
Column C = Total enquiries - e.g. "Retail" "Sold"
Column D = Total enquiries - eg "Retail" "Cancelled"
Column H = Total $ amount
Column K = Text - e.g Sold

I'm trying to find a formula that will calculate the total enquiries listed on the spreadsheet that are of "Retail" type & Those that "Sold", as well as enquiries that are "Retail" and were "Unsold"

I am using the count formula's but they are not working...=COUNT($B3:$B35,"RETAIL", $K3:$K35,"SOLD").....

[XOR LX]

Could you please upload a workbook?

Make sure to remove confidential/sensitive information first, and be sure to include your expected results.

Regards

[amyk82]

Please find attachment - i hope I've don't this correctly!

example.xls

[XOR LX]

Thanks.

I notice your profile gives 2010, yet you've attached an .xls. Does a solution need to be compatible with Excel 2003?

Also, can you clarify which your expected results are in this file? Can't seem to locate them.

Regards

[amyk82]

Sorry yes my work version is 2010, home version is 2003.

2010 is priority!

I'm wanting the formula to count how many retail enquiries were sold and how many were unsold...I can get it to count the retail enquiries but not the second condition...

[XOR LX]

Sorry - I meant, what are the manually-calculated results that you would like to see, and in which cells should they be placed?

Regards

[amyk82]

Oh sorry!

So I'm looking for the totals to be - using retail for example

Retail
Total Sold Enquiries 4
Total Sold $ 15,650
Total Unsold Enquiries 2
Total Unsold $ 4795.00
Total Sold clients 9
Total Unsold Clients 8

[XOR LX]

Sorry - which cells should these results go in?

Regards

[amyk82]

B:35 - Total Sold Enquiries 4
C:35 - Total Sold $ 15,650
D:35 - Total Unsold Enquiries 2
E:35 - Total Unsold $ 4795.00
F:35 - Total Sold clients 9

[XOR LX]

Thanks. I don't know how you get your results for Total Unsold or Total Sold Clients (I get $4785.00 and 11) but anyway:

B35: =COUNTIFS($B$3:$B$21,$A35,$I$3:$I$21,"SOLD")

C35: =SUMIFS($H$3:$H$21,$B$3:$B$21,$A35,$I$3:$I$21,"SOLD")

D35: =COUNTIFS($B$3:$B$21,$A35,$I$3:$I$21,"UNSOLD")

E35: =SUMIFS($H$3:$H$21,$B$3:$B$21,$A35,$I$3:$I$21,"UNSOLD")

F35: =SUMIFS($F$3:$F$21,$B$3:$B$21,$A35,$I$3:$I$21,"SOLD")

Note: none of these will work in Excel 2003. Let me know if you need alternatives that will.

Regards

[amyk82]

Thanks very much!!!

I've spent too many hours again in front of the computer so I'm probably no making much sense!

[XOR LX]

You're welcome!