Complicated question with possible easy answer

wrestlemy69

New Member
Joined
Jan 24, 2017
Messages
8
Office Version
  1. 2021
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Noob here.
I see 2 possible solutions for the same problem. So first i will try to describe it simply with the possibility to get a simple answer.

I have 4 drop down list. first list is ALL, feed, treats, equipment. second is ALL, chickens, dogs, cats. Third is jan, feb, mar, ....... 4th is 2022, 2023, 2024, ......... on another sheet i have cells with list for each, type, animals, date. I created a sumifs that works for each item individually. But i have not figured out a way to do the ALL for any list.

Is there a way to redifne the word all? meaning all will sum all of the words in another table. Hopefully i explained it well.

Ok so if that is not possible or understandable, here is the next possible solution i see. I could not figure out a way to add the If infront of the Sumifs.

here is the working one
Excel Formula:
=SUMIFS(Bought!G2:Bought!G332, Bought!I2:Bought!I332, ">="&'Date sheet'!I1,  Bought!I2:Bought!I332, "<="&'Date sheet'!J1, Bought!C2:Bought!C332, B5, Bought!A2:Bought!A332, B4)

B4 is the type list
b5 is the animal list

I feel like i am having a hard time explaing it well.

Thanks in advance for any help
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe
Excel Formula:
=SUMIFS(Bought!G2:G332, Bought!I2:I332, ">="&'Date sheet'!I1,  Bought!I2:I332, "<="&'Date sheet'!J1, Bought!C2:C332,if(B5="All","*",B5), Bought!A2:A332, if(B4="All","*",B4))
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe
Excel Formula:
=SUMIFS(Bought!G2:G332, Bought!I2:I332, ">="&'Date sheet'!I1,  Bought!I2:I332, "<="&'Date sheet'!J1, Bought!C2:C332,if(B5="All","*",B5), Bought!A2:A332, if(B4="All","*",B4))
Thank You so much! That seemed to work perfectly!
 
Upvote 0
Is the 3srd drop down actual text like Jan, Feb etc or is it numbers?
 
Upvote 0
Is the 3srd drop down actual text like Jan, Feb etc or is it numbers?
It is actual text. I was actually just trying to figure out how to do an all for it and year as well. but that gets a bit more complicated. on date sheet i use CONCATENATE as well as vlookup.
 
Upvote 0
I did a small test and it worked. I will do a larger test.

datesheetI1 = VLOOKUP(G1,A1:D39,4,0)
datesheetJ1 = VLOOKUP(G1,A1:E39,5,0)
G1 = CONCATENATE('Simple spent'!B6,'Simple spent'!B7) B6 is month B7 is year
a1=oct2022 d1=10/1/2022 e1=10/31/2022 it continues down for each month and year

Not sure this helps, but couldnt figure out a way to upload book
 
Upvote 0
If you're converting the 3rd & 4th drop downs into an actual date then that should be ok.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top