2 Sum Ifs for a # & text

[Statz]

Hello.

Attached is a document I'm trying to gather information for. All the actual data is in "groceries15" tab. I want a cover page to have a snap shot of the data in that tab.

I highlighted in yellow where I started to try to create a formula. I need to capture the sum of all items bought in 2015 listed on the left (column A).

I've done countifs but not sumifs.

When "almonds" is listed in the "groceries15" tab for the year 2015 sum that amount. Then hopefully the formula allows me to just click on what i'm trying to find so that I can easily copy down. I know some formulas can be set up so I could copy down & across and capture the correct information if the $ signs are in the right spot (i can figure that part out).

Thanks

[Pete_UK]

You can put this formula in E3:

=SUMIFS(Groceries15!$H:$H,Groceries15!$D:$D,$A3,Groceries15!$A:$A,E$2)

and this one in F3:

=COUNTIFS(Groceries15!$D:$D,$A3,Groceries15!$A:$A,E$2)

Format F3 as General, then copy those two formulae down. Then you can copy the block of cells from E3:F42 into G3, I3, K3 for the other years. You can also copy that block into C3, but as you have no data for 2016 you will get zero for all the items.

Hope this helps.

Pete

[FlameRetired]

Then for the totals (column B) try changing your formula to

Please Login or Register  to view this content.

[Statz]

Pete_Uk. Thank you!

FlameRetired. I was wondering why you gave me that formula at first. I was not expecting that so I appreciate the new way of doing that. I originally meant to just sum all the columns one by one to ignore the columns giving a non $ amount. But if i understand that formula correctly since the years listed are #'s & the " # times" is not numbers it only adds the #'s.


Thanks to you both!

[FlameRetired]

You are welcome. Thank you for the feedback and the rep.