Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
I need an Excel Formula, Multiple counting - help!?
I want to see how many times I feed a certain animal type on my farms on a day of the week. This formula will count all of the "am" "noon" & "pm" in one column but only for "dog".
In this example for the Tuesday column, for "dogs" there are 2 "am" and 1 "noon". So referencing the feed legend that would be 2x5 and 1x3 = 13. I feed all dogs on a Tuesday a total of 13 times.
-----------------------------------
MON TUE WED THU etc.
Dogs 13
-----------------------------------
I need the formula to reside in the cell where you see the number 13. Any help would be greatly appreciated!
===========================================
FEED A DAY:
am = 5
noon= 3
pm = 2
REFERENCE:
A B C D E
MON TUE WED THU etc.
dogs am am pm pm
cats am am noon pm
cats noon noon noon pm
dogs am noon pm pm
birds am am am pm
dogs am am pm pm
THANK YOU - THANK YOU - THANK YOU - THANK YOU -
2 Answers
- expletive_xomLv 71 decade agoFavorite Answer
well dean,
i obviously cant do all of this for you.
but i can start you off.
you can use the sumproduct() formula
so if Tuesday is in the C column
copy&paste these formulas
=SUMPRODUCT((A2:A7="dogs")* (C2:C7="am"))*5
that should give you your 10 in AM
and then this should give you your 3
=SUMPRODUCT((A2:A7="dogs")* (C2:C7="noon"))*3
- you need to delete my hardcoded formula, and change everything to cell references.
- if you have a unique list...like dogs in J1 and noon in K1 and the 3 from the table in L1
=SUMPRODUCT((A2:A7=J1)* (C2:C7=K1))*L1
should look something like that.
- and you can add tuesday together in 1 cell by adding the sumproduct() formulas with the + sign.
- personally, i wouldnt use any of these formula, i would use a pivot table, but you have that legend that makes it to complicated to answer here.
good luck.
- Anonymous4 years ago
do only 3 COUNTIF's for each row of archives, one for x, y and z. in case you do no longer prefer to verify all those columns you additionally could make a clean column it incredibly is the sum of the three COUNTIF ones and conceal all however the sum column when you place all of the formulae. Puzzler's is a similar thought, only plenty greater convenient :P
