SUMIFS AND AVERAGEIFS

[EllaDani]

Create a formula in cell B18 that will sum up all amounts in D8:D16 where Unit Price is greater than or equal to 15
Create a formula in cell B21 that will get the average of all amounts in D8:D16 of those items that starts with the letter "T" with a unit price of greater than 70.

I tried various formulas but I either got errors or the answer is not the same as the first picture. The answer in sumifs should be 1950 as advised by my professor but I keep on getting 6900 using this formula =SUMIFS(D8:D16,C8:C16,">=15") and I get an error when I use this on AVERAGEIFS =AVERAGEIFS(D8:D16, A8:A16,"T",C8:C16,">70)") Please Heeelppp Thank you and God Bless..

 

[Z51]

If you simply add up column D for all items with a unit price >=15, then you do indeed get 6900. Are you sure there wasn't other criteria that would limit it to, perhaps, just the Sandwiches and Flyer (1950 total)? Maybe equal to 15, not greater than or equal? Also, when there is only one criteria, you can use SUMIF rather than SUMIFS.

=SUMIF(C8:C16,">=15",D8:D16)

As for the average, there appears to be another issue - there is only ONE item with a unit price greater than 70 and starts with the letter T - the Tarpauline. So, the average would simply be 450. If you added 7500 (T-shirts, also starts with T) and the Tarpauline, you'd get 7950, which when divided by two items would return 3975.

Something just isn't right with the data set, or with the professor.

 

[AhoyNC]

Also, your syntax is wrong for T in the AVERAGESIFS formula. If you want items that start with T it should be "T*". You need to add the wild card "*" after the T.